Running total by month with sum by category with no data for some months

[PivotRiot]

Hi, 

I want to make a running total by month with sum by category. The category itself is not shown in the report. It seems that I have found the code for the RT I was looking for, even two ways of doing it, but it doesn't add up two different products month by month correctly if the one of the product was not sold for a specific month. See example below.

Sample file is attached: https://www.dropbox.com/scl/fi/9r05a38rzwhgqv25ycjoo/Demo1.xlsx?rlkey=81qxdzeys6a8r2ittezshufpe&dl=0

First option:

= SUMX(
VALUES(Data[Product]);
	CALCULATE(Data[Sales]; FILTER(ALLSELECTED('Calendar'[Date]); 'Calendar'[Date] <= MAX(Data[SalesDate]))))

Second option:  

= SUMX(
VALUES(Data[Product]);
	CALCULATE(Data[Sales];FILTER(ALLSELECTED('Calendar'[Date]); ISONORAFTER( 'Calendar'[Date]; MAX(Data[SalesDate]);DESC)))
	)

Here are the two examples of the formulas doesn't add up correctly and one example when it works well:

Example 1: Product A has sales in the month 1 and 3. Product B only has sales in the month 2. I need a RT accumulated for both, but the code above seems to break RT from month 2:

Example 2: Product C has sales in the months 1 and 3. Product D has sales in the months 2 and 3. The RT breaks on the month 2, I guess because product C has no sales in the month 2. The expected result is to accumulate both products.

Example 3: Product E has sales in the month 1 only. Product F has sales in the months 1, 2 and 3. In this case the output seems to meet expectations and works correct.

Please help me to figure out how to fix the DAX code the way it deals correctly with the examples 1 and 2.

Greatly appreciate your support. 

Thanks.

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file.  Show the expected result very clearly.  Sharing images does not help.

[PivotRiot]

Hello Ashish, please find the link to the demo file: https://www.dropbox.com/scl/fi/9r05a38rzwhgqv25ycjoo/Demo1.xlsx?rlkey=81qxdzeys6a8r2ittezshufpe&dl=0

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[PivotRiot]

Hi Ashish, may I ask you please to explain here what was wrong with the code or attach an .xlsx file with correct code? I can't open .pbix files and only can use PowerPivot in Excel.

Thank you.

[Ashish_Mathur · Accepted solution]

Create a Calendar Table with calculated column formlas for Year, Month name and Month number.  Sort the Month name coumn by the Month number.  Create a relationship from the Date column of your Data table to the Date column of the Calendar Table.  To your slicer, drag Year from the Calendar Table and select a Year.  To your table, drag Month name from the Calendar Table.  Writ these mesures

S = sum(Data[Sales])

Running total = calculate([S],datesytd(calendar[date],"31/12"))

Hope this helps.

[PivotRiot]

Thank you, this works! I just added an IF condition before the measure you proposed to exclulde blanks for the future months.

[Ashish_Mathur]

You are welcome.

[v-shex-msft]

HI @PivotRiot,

I think you may need a disconnected date table and use it as the axis, then you can wire Dax expression to calculate, and looping table records based on new table date field.

They will help you to expand the calculations even if raw table not existed correspond month of records.

Regards,

Xiaoxin Sheng

[PivotRiot]

Please check my sample data file, I have already initially added a calendar table:

https://www.dropbox.com/scl/fi/9r05a38rzwhgqv25ycjoo/Demo1.xlsx?rlkey=81qxdzeys6a8r2ittezshufpe&dl=0