Running total in Power Pivot based on other measure with missing values in certain months

[PivotRiot]

Hi there, 

I am sorry to address with the same question for the 3rd time, but it seems I can't explain what I need in a proper way.

I have tried to make a running total based on a measure, see the file by the link.

My problem is that when I add up the items which has only sales in the middle month, here in February, it doesn't show the running total in a correct way. 

@Ashish_Mathur proposed a solution in his file, but this doesn't add up by product by month using a measure called [Sales Total] which is a quantity sold in 2024 multiplied by the average price of 2023, so for products A+B in year 2024 the running total is shown incorrectly:

Jan 1 944 655,68

Feb 3 141 570,12

Mar 6 100 828,77

But it should be the following:

Jan 1 717 254

Feb 5 071 157

Mar 7 684 370

With my code

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

I can only have the following result:

Jan 1 717 254 (only the value of product A, because no sales of product B in Jan)

Feb 3 353 903 (only the value of product B, not adding product A, which had no sales in Feb)

Mar 4 330 467 (only cumulative of proudct A for the months Jan and Mar not counting product B, which had sales only in Feb)

Total 7 684 370 (adding both product A and B correctly).

So I am still stuck on this. Could anyone help?

These are the two topics I have already started but couldn't get the acceptable solution: one, two.

My other problem is that I need to use PowerPivot, not a PowerBI and it seems that some code proposed for PowerBI doesn't show correct results for Power Pivot.

[Greg_Deckler]

@PivotRiot Maybe:

Measure = 
  VAR __Date = MAX( 'Date'[SalesDate] )
  VAR __Table = SUMMARIZE( FILTER( 'Data', [Date] <= __Date ), [Product], [Date], "__Value", [SalesTotal] )
  VAR __Result = SUMX( __Table, [__Value] )
RETURN
  __Result

[PivotRiot]

Hi Greg, thanks for your quick response. I tried this in PowerPivot but only get a blank cells. May I ask you please for PowerPivot solution or modify and attach my original xls file?

[Greg_Deckler]

@PivotRiot I got what appears to be a running total. Had to rewire a bunch of underlying measures. See attached below signature.

[PivotRiot]

Hi Greg, 

not sure, I get what solution do you propose in the attached file. What modification to the measures [RT Sales1] or [RT Sales2] can you propose to make it look like in the column *What I need:* I have added manually.

The [AvgPrice 2023] and the [RT Sales1] measures are calculated first by product A and B in separate pivot table. 

So [RT Sales1] works correctly by product, but doesn't work by product by month together.

[v-yiruan-msft]

@Greg_Deckler Thanks for your contribution on this thread.

Hi @PivotRiot ,

Did you get the solution? If no, could you please provide some sample data (exclude sensitive data) with Text format and the calculation logic of measure [AvgPrice 2023], [RT Sales1] and [What I need] with the speicial examples and screenshot? It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And it is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

In addition, you can refer the following links to try to solve your problem...

Why Your Total Is Incorrect In Power BI - The Key DAX Concept To Understand

Dax for Power BI: Fixing Incorrect Measure Totals

Best Regards