Hi
I’m trying to make an cumulative sum of production quantities throughout the year.
I have created a DAX measure in PowerPivot that works fine on the totals as illustrated in the below table, however, on the detailed level I get blanks which causes issues when I want to compare the YTD figure for May with the YTD figure for October on a detailed level.
My DAX formula looks like this:
Actual Production:=IF(MIN('SCP calendar'[SCP Date])<=CALCULATE(MAX(Actual_Production[Date]);ALL(Actual_Production)); CALCULATE(SUM(Actual_Production[Qty in BC or Pack]);FILTER(ALL('SCP calendar'[SCP Date]);'SCP calendar'[SCP Date]<=MAX(Actual_Production[Date]))))
[Actual Production] is a transaction table of with production data
[SCP calendar] is the calendar table.
<tbody>
</tbody>
Anyone who have an idea on how I get the previous months figure repeated in the blank cells?
I’m trying to make an cumulative sum of production quantities throughout the year.
I have created a DAX measure in PowerPivot that works fine on the totals as illustrated in the below table, however, on the detailed level I get blanks which causes issues when I want to compare the YTD figure for May with the YTD figure for October on a detailed level.
My DAX formula looks like this:
Actual Production:=IF(MIN('SCP calendar'[SCP Date])<=CALCULATE(MAX(Actual_Production[Date]);ALL(Actual_Production)); CALCULATE(SUM(Actual_Production[Qty in BC or Pack]);FILTER(ALL('SCP calendar'[SCP Date]);'SCP calendar'[SCP Date]<=MAX(Actual_Production[Date]))))
[Actual Production] is a transaction table of with production data
[SCP calendar] is the calendar table.
Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Grand total | |
Product group 1 | 5 | 15 | 28 | 32 | 40 | 50 | 62 | 68 | 71 | 71 |
Product A | 4 | 6 | 10 | 10 | ||||||
Product B | 5 | 12 | 20 | 20 | ||||||
Product C | 5 | 6 | 12 | 16 | 22 | 28 | 32 | 38 | 41 | 71 |
Grand Total | 5 | 15 | 28 | 32 | 40 | 50 | 62 | 68 | 71 | 71 |
<tbody>
</tbody>
Anyone who have an idea on how I get the previous months figure repeated in the blank cells?