Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a dataset that is not normalized. One of the columns contains running total of sales. Based on this column, I want a Sales column that gives sales for each month. This column will help me to do calculations like ytd, qtd and mtd
Job YYYYMM Running Total Sales (Desired Col)
A 201803 450 150
B 201803 300 250
A 201712 300 50
A 201710 250 250
B 201802 50 50
Solved! Go to Solution.
Hi @svishwanathan,
Please try this formula:
Sales = 'Job Sales'[Running Total] - LOOKUPVALUE ( 'Job Sales'[Running Total], 'Job Sales'[Job], 'Job Sales'[Job], 'Job Sales'[YYYYMM], CALCULATE ( MAX ( 'Job Sales'[YYYYMM] ), FILTER ( ALLEXCEPT ( 'Job Sales', 'Job Sales'[Job] ), 'Job Sales'[YYYYMM] < EARLIER ( 'Job Sales'[YYYYMM] ) ) ) )
Best regards,
Yuliana Gu
Hi @svishwanathan,
Please try this formula:
Sales = 'Job Sales'[Running Total] - LOOKUPVALUE ( 'Job Sales'[Running Total], 'Job Sales'[Job], 'Job Sales'[Job], 'Job Sales'[YYYYMM], CALCULATE ( MAX ( 'Job Sales'[YYYYMM] ), FILTER ( ALLEXCEPT ( 'Job Sales', 'Job Sales'[Job] ), 'Job Sales'[YYYYMM] < EARLIER ( 'Job Sales'[YYYYMM] ) ) ) )
Best regards,
Yuliana Gu