Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
hey Folks,
I'm trying to get the cumulative sum - WTD per QTR and was able to get it using below formula:
<CSUM_QTY_IN = CALCULATE (SUM(Sheet1[VALUE]), FILTER(Sheet1, Sheet1[Index]<=EARLIER(Sheet1[Index])&&Sheet1[QTR]=EARLIER(Sheet1[QTR])))>
However, since its a cumulative sum per qtr, it seems to populate future weeks as below. Can y'all please help to advice how to deal with such scenarios? I just want my formula to populate till the current week but looks like it evaluating for the entire qtr.
Thanks,
Solved! Go to Solution.
Hi @alvin_alpha
You can use the below expresion, however you need to adjust it to your data model.
Sales Running Total = VAR mc = MAX('Calendar'[Date]) VAR mf = CALCULATE(MAX(Sales[OrderDateKey]), ALL(Sales)) >= mc RETURN IF( mf, CALCULATE( [Sales], FILTER( ALL('Calendar'[Date]), 'Calendar'[Date] <= mc ) ) )
Hope this helps
Mariusz
hi @Mariusz ,
thank you for the suggestion. I did miss to mention that my dates [QTR & WORKWEEK] are text fields due to a different WorkWeek definition we use. I did try out your formula but doesnt seem to work probably due to the date factor. let me play with it still.
thanks,
Hi @alvin_alpha
You can use the below expresion, however you need to adjust it to your data model.
Sales Running Total = VAR mc = MAX('Calendar'[Date]) VAR mf = CALCULATE(MAX(Sales[OrderDateKey]), ALL(Sales)) >= mc RETURN IF( mf, CALCULATE( [Sales], FILTER( ALL('Calendar'[Date]), 'Calendar'[Date] <= mc ) ) )
Hope this helps
Mariusz
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
98 | |
81 | |
65 | |
62 |
User | Count |
---|---|
147 | |
116 | |
104 | |
88 | |
65 |