Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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
User | Count |
---|---|
89 | |
88 | |
75 | |
67 | |
58 |
User | Count |
---|---|
137 | |
110 | |
91 | |
84 | |
69 |