Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I'm trying to perform a dynamic calculation. the premis is I want to see when a customer first used a promotional code, and how much the have spent with us after.
I have calculated the the first promotion date using the following DAX
Orders After Min Promo =
VAR amt = SUM ( 'report v_fct_sales_order_promo_customers'[sales_order_item_total_amount])
VAR mcd = MIN ('report v_dim_sales_order_promo_codes'[CompletedDate])
RETURN
CALCULATE( amt, mcd > 'report v_fct_sales_order_promo_customers'[date_date])
see screen shot below, the min date 08th december - so I would expect the DAX to calculate the 3 lines on the 13th December which total £51.14 but its not returing the expected result it seems to be ignoring the date and just giving me the total. I have included screen shots of my model and report page with areas highlighted.
Solved! Go to Solution.
CALCULATE(amt,Filter(all(DAtedim),datesdim(date)>mcd))
You need to created Date Table to connect to the fact table to achieve above
Proud to be a Super User!
CALCULATE(amt,Filter(all(DAtedim),datesdim(date)>mcd))
You need to created Date Table to connect to the fact table to achieve above
Proud to be a Super User!
@VijayP you are a genius and a gentleman, thank you very much
Result = CALCULATE(SUM ( 'report v_fct_sales_order_promo_customers'[sales_order_item_total_amount]), FILTER(datetable,DateTable[Date] > [MCD] ) )
@Magpie_Rob You are Welcome, I Appreciated your Kudos by pressing 👍Icong. Thanks
Proud to be a Super User!
@Magpie_Rob ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |