Blanks in DAX cumulative sum

[Bloch1811]

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.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[TD]Grand total[/TD]
[/TR]
[TR]
[TD]Product group 1[/TD]
[TD]5[/TD]
[TD]15[/TD]
[TD]28[/TD]
[TD]32[/TD]
[TD]40[/TD]
[TD]50[/TD]
[TD]62[/TD]
[TD]68[/TD]
[TD]71[/TD]
[TD]71[/TD]
[/TR]
[TR]
[TD]Product A[/TD]
[TD][/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD]6[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Product B[/TD]
[TD][/TD]
[TD]5[/TD]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]Product C[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]12[/TD]
[TD]16[/TD]
[TD]22[/TD]
[TD]28[/TD]
[TD]32[/TD]
[TD]38[/TD]
[TD]41[/TD]
[TD]71[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD]5[/TD]
[TD]15[/TD]
[TD]28[/TD]
[TD]32[/TD]
[TD]40[/TD]
[TD]50[/TD]
[TD]62[/TD]
[TD]68[/TD]
[TD]71[/TD]
[TD]71[/TD]
[/TR]
</tbody>[/TABLE]



Anyone who have an idea on how I get the previous months figure repeated in the blank cells?

 

[gazpage]

https://msdn.microsoft.com/en-us/library/ee634400.aspx

Can't you just follow this example? I can't follow your formula at all. Can you put it through daxformatter.com

 

[Bloch1811]

Hi gazpage

Thank you for taking your time to answer. The TOTALYTD function gave the same issues as the formula I had.

However, I found a solution using the formula below:

Actual prod - cumulative :=
CALCULATE (
SUM ( Actual_Production[Qty in BC or Pack] );
FILTER (
ALLSELECTED ( Actual_Production );
AND (
Actual_Production[Date] <= MAX ( 'SCP calendar'[SCP Date] );
YEAR ( Actual_Production[Date] ) = YEAR ( MAX ( 'calendar'[Date] ) )
)
);
VALUES ( MasterData )
)

The key was to add the VALUES-section with the table I use to split up the total.