Forum Discussion

UKNSI-Powerbi's avatar
UKNSI-Powerbi
Frequent Visitor
4 years ago
Solved

PowerBi - Running totals

Hi everyone,

 

I need to perform a running total on a pivot table based on calendar.

 

Year1 Net is calculated with the following formula 100+10-5=105

Year 2 because a running total use Year1 as reference 105+5-40=70

 

And so on..

 

Thanks in advance

 

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    Thank you for your message.

    Did you open the file that I attached?

    Or, are you using different one and using the meaasure that I created?

     

    I am seeing the below screenshot.

     

     

    For checking purpose, try the below.

    Net measure v2: = 
VAR _initialstock =
     MAX ( Sheet1[Initial Stock] )
VAR _newtablesupplydemand =
    SUMX (
        FILTER (
            ALL ( Sheet1 ),
            Sheet1[Item] = MAX ( Sheet1[Item] )
                && Sheet1[Week YYWW] <= MAX ( Sheet1[Week YYWW] )
        ),
        Sheet1[Supply] - Sheet1[Demand]
    )
RETURN
    _initialstock + _newtablesupplydemand

8 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    I am not sure how your data model looks like, but I tried to create a sample pbix file like below.

    Please check the below picture and the attached pbix file.

    I hope this helps to provide some ideas on how to create a solution for your data model.

     

     

     

    Dem measure: = 
SUM( Data[Demand] )
    Supply: = 
SUM( Data[Supply] )
    Net: =
SUM ( 'Item'[Initial stock] )
    + CALCULATE ( [Supply:], 'Year'[Year] <= MAX ( 'Year'[Year] ) )
    - CALCULATE ( [Dem measure:], 'Year'[Year] <= MAX ( 'Year'[Year] ) )

     

     

    • UKNSI-Powerbi's avatar
      UKNSI-Powerbi
      Frequent Visitor
      4 years ago

      Hi,

       

      I tried your solution but I had an unexpected problem on function MAX: A function 'MAX' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

       

      This is the measure script, different table but exacrly same principle you suggested:

      Net measure: =
      SUM ( 'DEMAND&SUPPLY_REPORT'[Initial Stock] )
      + CALCULATE ( [Supply measure:], 'DEMAND&SUPPLY_REPORT'[Report_Week] <= MAX('DEMAND&SUPPLY_REPORT'[Report_Week] ) )
      - CALCULATE ( [Dem measure:], 'DEMAND&SUPPLY_REPORT'[Report_Week] <= MAX('DEMAND&SUPPLY_REPORT'[Report_Week] ) )
       
      I extracted an example from tyhe dataset and I highlighted the calculation

       

       
       
       

       

       

       

       

       

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Icon for Super User rankSuper User
        4 years ago

        Hi,

        Please share your sample pbix file's link here, and then I can try to look into it to come up with a more accurate solution for your data model.

        Thanks.