PowerBi - Running totals

[UKNSI-Powerbi]

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

[Jihwan_Kim]

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]

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]

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]

I sent you a PM through Linkedin too Kim

[Jihwan_Kim]

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]

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 · Accepted solution]

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

[UKNSI-Powerbi]

Hi Kim,

I confirm I used the one you attached and this one it doesn't work. However the good new is the last measure you posted it works great and I can accept as solution. 

Thanks again for the effort and time spent!!

Giovanni