Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 f...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

     

amitchandak's avatar
amitchandak
Super User
6 years ago

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