My scenario is 2 fact tables (point & trans) connecting to a dimension table (profile)
point table is filtered by calendar table (e.g. 3-Aug-2023 to 4-Aug-2023), there are 124 members with this date selection
a. getting the regist date of these 124 members from profile table
b. getting the last order date from trans for these 124 members from trans table
when regist date of member >= 3-Aug-2023, would call this member group as A and get the distinct count of the members
when regist date of member < 3-Aug-2023 and last order date >= 3-Aug-2022, would call this membes group as B and get the distinct count of the members
when regist date of member < 3-Aug-2023 and last order date < 3-Aug-2022, would call this member group as C and get the distinct count of the members
when regist date of member < 3-Aug-2023 and last order date is null, would call this member group as D and get the distinct count of the members
have written 2 measures [_Last_Order_Date], [_Last Order Date (for points member)] about this, but not sure how to proceed for [_Historical Purchase Group Count]
have uploaded pbix file to onedrive , could anybody help to get this work? https://1drv.ms/u/s!ArckVIvNaAEBhSMC_3bknq2TD2S0?e=JIzQkT
Data model _Last_Order_Date _Last Order Date (for points member) _Historical Purchase Group Count1 _Historical Purchase Group Count2
