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,

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

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

    Hi,

    Thank you for your message.

    Please check the attached file, if it suits your requirement.

     

    Net measure: = 
VAR _initialstock =
    CALCULATE ( MAX ( Sheet1[Initial Stock] ), Sheet1[Item] = MAX ( Sheet1[Item] ) )
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
    • UKNSI-Powerbi's avatar
      UKNSI-Powerbi
      Frequent Visitor
      4 years ago

      Hi,

       

      Thanks for the effort, I tried but it seems not working; it return the same error as it seems it doesn't like MAX function:

      Feedback Type:
      Frown (Error)

      Timestamp:
      2022-07-19T19:02:08.6655614Z

      Local Time:
      2022-07-19T20:02:08.6655614+01:00

      Session ID:
      d5787985-66a9-4cf7-b6ef-1ab380e9c4c9

      Release:
      August 2021

      Product Version:
      2.96.1061.0 (21.08) (x64)

      Error Message:
      MdxScript(Model) (6, 63) Calculation error in measure 'Sheet1'[Net measure:]: A function 'MAX' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

      OS Version:
      Microsoft Windows NT 10.0.19044.0 (x64 en-GB)

      CLR Version:
      4.7 or later [Release Number = 528372]

      Peak Virtual Memory:
      102 GB

      Private Memory:
      595 MB

      Peak Working Set:
      815 MB

      IE Version:
      11.789.19041.0

      User ID:
      348c35c7-ee62-484a-9758-5f7a69219fef

      Workbook Package Info:
      1* - en-GB, Query Groups: 0, fastCombine: Disabled, runBackgroundAnalysis: False.

      Telemetry Enabled:
      True

      Snapshot Trace Logs:
      C:\Users\GBRUTTO.UKNSI\AppData\Local\Microsoft\Power BI Desktop\FrownSnapShotad57310a-e450-4e97-82b5-42ec6f3e561c.zip

      Model Default Mode:
      Import

      Model Version:
      PowerBI_V3

      Performance Trace Logs:
      C:\Users\GBRUTTO.UKNSI\AppData\Local\Microsoft\Power BI Desktop\PerformanceTraces.zip

      Enabled Preview Features:
      PBI_JsonTableInference
      PBI_NewWebTableInference
      PBI_ImportTextByExample
      PBI_ExcelTableInference
      PBI_rdlNativeVisual

      Disabled Preview Features:
      PBI_shapeMapVisualEnabled
      PBI_SpanishLinguisticsEnabled
      PBI_qnaLiveConnect
      PBI_azureMapVisual
      PBI_dataPointLassoSelect
      PBI_compositeModelsOverAS
      PBI_dynamicParameters
      PBI_enhancedTooltips
      PBI_enableWebView2

      Disabled DirectQuery Options:
      TreatHanaAsRelationalSource

      Cloud:
      GlobalCloud

      DPI Scale:
      100%

      Supported Services:
      Power BI

      Formulas:


      section Section1;

      shared Sheet1 = let
      Source = Excel.Workbook(File.Contents("C:\Users\GBRUTTO.UKNSI\Documents\running total example.xlsx"), null, true),
      Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
      #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
      #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Item", type text}, {"Initial Stock", Int64.Type}, {"Week YYWW", Int64.Type}, {"Demand", Int64.Type}, {"Supply", Int64.Type}}),
      #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Item] = "0042001599R0000"))
      in
      #"Filtered Rows";

       

       

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Super User
        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