Calculate running total based on Sort Column

[Anonymous]

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?

[amitchandak]

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

[Anonymous · Accepted solution]

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

[Anonymous]

Thank you so much. This worked exactly as stated and I used the ALLEXCEPT filter to apply the running total to the applicable name.

[Anonymous]

Can you post the formula you used?  I'm not getting the running total, just the total on each row when using the ALLEXCEPT filter.  Table looks like below now - 

User	Amt	Running Total
1	100	250
1	100	250
1	50	250

[Ashish_Mathur]

Hi,

Do you have a Date column?  If yes, then share that dataset.

[Anonymous]

Here's the table I'm using 'User_Payments' - 

User_ID	Tran_Date	Tran_Amt	Running_Deposits
100	7/1/2021	100	300
100	7/15/2021	125	300
100	7/18/2021	75	300
105	6/1/2021	120	220
105	6/30/2021	100	220
110	7/15/2021	50	50
115	6/15/2021	115	200
115	7/1/2021	85	299

Running_Deposits is a calculated column using this DAX code : 

Running_Deposits =

CALCULATE (

SUM ( 'User_Payments'[Tran_Amt] ),

ALLEXCEPT (

'User_Payments',

'User_Payments'[User_ID]

),

'User_Payments'[User_ID]

= EARLIER ( 'User_Payments'[User_ID] )

)

I'm getting the total for each User_ID, but not the running total.  I'd like results like this:

User_ID	Tran_Date	Tran_Amt	Running_Deposits
100	7/1/2021	100	100
100	7/15/2021	125	225
100	7/18/2021	75	300
105	6/1/2021	120	120
105	6/30/2021	100	220
110	7/15/2021	50	50
115	6/15/2021	115	115
115	7/1/2021	85	200

I've tried <= and >= operators in the filters, but that does not yeild better results.  

Thanks! 

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[mohammadyousaf]

I am so so thankful to you, I was looking for this solution for hours. 

[Ashish_Mathur]

You are welcome.

[Anonymous]

Ashish,

Thank you very much for sharing this.  I'll work with it to get it into a calculated column rather than a measure as this is really just one component of a much larger need.

[Ashish_Mathur]

You are welcome.