Running Total

[RLOH]

Hi

My excel table is downloaded from SAP,  there is column for Fiscal Year, and another column for Posting Period (which indicate 1, 2 , 3 etc representing the month).  May i know how to generate DAX for running total given that the posting period is not a date or a month?  Must i first turn the Posting Period into a date or month first before i can generate DAX ?

Rachel

[Greg_Deckler · Accepted solution]

@RLOH See if this helps: Better Running Total - Microsoft Power BI Community. In your case it would be something like:

Better RT = 
    VAR __Year = MAX('Table'[Fiscal Year])
    VAR __Month = MAX('Table'[Posting Period])
    VAR __YearMonth = __Year * 100 + __Month
    VAR __Table = FILTER(ALLSELECTED('Table'), [Fiscal Year] * 100 + [Posting Period] <= __YearMonth )
RETURN
    SUMX(__Table,[Value])

[SANDRAVELASQUEZ]

Hello 

I need to create a running total to count de the diffrent  amount of id according to a list 

i have to tables one with the right order  and the other with the values

i need to make the column running total

[Ashish_Mathur]

Hi,

What should the order be for generating the Running total?

[SANDRAVELASQUEZ]

Hello,

The order must be given by the column num.

I would prefer it it be a measure so it can be affected by slicers.

In a dax it wont be affected by slicers

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[RLOH]

@Greg_Deckler , i also have another question related to this running total.  Now that i have created new measure for running total which is the actual year-to-date number, i have another column called "Plan YTD" and i want to subtract Plan Ytd from this actual year-to-date number.  When i use the Calculate DAX and try to find the column , there is no such column ..... so can i actually subtract the new created measure ?  

[Ashish_Mathur]

Hi,

Share the download link of the PBI file and show the expected result.

[RLOH]

@Ashish_Mathur , may i know how to share file with you ? 

[Ashish_Mathur]

Hi,

Upload the file to Google Drive and share the download link here.  Show the expected result very clearly.

[RLOH]

hI,

This is the PBIX file that i am trying to work on.

https://drive.google.com/file/d/1ALCir7Vhs1sEqvJ8jTzpr7820mv26D0C/view?usp=sharing

This is the excel spreadsheet that contain the data and the dashboard (the result)

https://docs.google.com/spreadsheets/d/1clhCuyMR2UQFCjbVjCz3GXHzkFu4ABtu/edit?usp=share_link&ouid=10...

[Ashish_Mathur]

Hi,

See if my solution here helps.

[RLOH]

@Ashish_Mathur unfortunately, the link that you are provided is blocked by my company safety network because the site is not secured.  Any chance you can upload the file in this PowerBI Community platform ? 

[Ashish_Mathur · Accepted solution]

File attached.  See signature.

[Greg_Deckler · Accepted solution]

@RLOH See if this helps: Better Running Total - Microsoft Power BI Community. In your case it would be something like:

Better RT = 
    VAR __Year = MAX('Table'[Fiscal Year])
    VAR __Month = MAX('Table'[Posting Period])
    VAR __YearMonth = __Year * 100 + __Month
    VAR __Table = FILTER(ALLSELECTED('Table'), [Fiscal Year] * 100 + [Posting Period] <= __YearMonth )
RETURN
    SUMX(__Table,[Value])

[RLOH]

@Greg_Decklerthanks for the solution, it works! However, the running total continue to add the value to another year,   Please refer below the column chart visual, you can see that the 2023 value continue to add on from year 2022..  But when i use Card Visual and filter only year 2023, the value for 2023 is correct.  May i know how to fix this ? 

Thanks for your help!