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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

@ 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!:
Mastering Power BI 2nd Edition

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

@ 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!:
Mastering Power BI 2nd Edition

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.