Forum Discussion

PivotRiot's avatar
PivotRiot
Frequent Visitor
2 years ago

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

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 t...
Need Help
Tips and Tricks
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
2 years ago

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's avatar
    PivotRiot
    Frequent Visitor
    2 years ago

    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's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      2 years ago

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

      • PivotRiot's avatar
        PivotRiot
        Frequent Visitor
        2 years ago

        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.