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.

	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?

 

[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.