Forum Discussion
Running Total
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
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])File attached. See signature.
14 Replies
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])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!
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 ?
Hi,
Share the download link of the PBI file and show the expected result.
Ashish_Mathur , may i know how to share file with you ?
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
Hi,
What should the order be for generating the Running total?
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
