Forum Discussion
SUM when date > dynamic date
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.
ModelReport Page
CALCULATE(amt,Filter(all(DAtedim),datesdim(date)>mcd))
You need to created Date Table to connect to the fact table to achieve above
4 Replies
CALCULATE(amt,Filter(all(DAtedim),datesdim(date)>mcd))
You need to created Date Table to connect to the fact table to achieve above
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
Magpie_Rob ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
