Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
RLOH
Frequent Visitor

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

2 ACCEPTED SOLUTIONS
Greg_Deckler
Super User
Super User

@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])

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

File attached.  See signature.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

14 REPLIES 14
SANDRAVELASQUEZ
New Member

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 totalCapture.PNG

Hi,

What should the order be for generating the Running total?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

 

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
RLOH
Frequent Visitor

@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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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...

Hi,

See if my solution here helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@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 ? 

File attached.  See signature.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Greg_Deckler
Super User
Super User

@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])

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@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!

 

RLOH_0-1679703397309.png

RLOH_0-1679712634026.png

 

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors