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 Ye...
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
Jihwan_Kim's avatar
Jihwan_Kim
Super 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
      Super 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.

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

        I sent you a PM through Linkedin too Kim