Forum Discussion
Calculate running total based on Sort Column
I have a table where I am trying to calculate a running total based on a sort column with an additional filter as follows. This would have typically been a standard YTD formula but the start of the fiscal year is in June and not calendar based hence the Month Sort.
Any thoughts on how best to build formula to do that?
Hi Anonymous
Use and modify the below Formula as per your need.
Running Total COLUMN = CALCULATE ( SUM ( 'table'[col1] ), ALL( 'table'), //ALLEXCEPT ( 'table', 'table'[] ) //If you want to group by any column 'table'[col2] <= EARLIER ( 'table'[col2] ) )https://www.wallstreetmojo.com/power-bi-running-total/
Did I resolve your issue? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!!
Regards,
Pranit
11 Replies
Hi Anonymous
Use and modify the below Formula as per your need.
Running Total COLUMN = CALCULATE ( SUM ( 'table'[col1] ), ALL( 'table'), //ALLEXCEPT ( 'table', 'table'[] ) //If you want to group by any column 'table'[col2] <= EARLIER ( 'table'[col2] ) )https://www.wallstreetmojo.com/power-bi-running-total/
Did I resolve your issue? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!!
Regards,
PranitAnonymous , first create an FY calendar with the correct sort. Hope you have dates
You have all 12 start months calendar here -https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calendar-1-5-Power/ba-p/1187441
Now use datesytd with year-end date of your choice
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"5/31")) //means year start in june
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"5/31"))To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.
