Cumulative (running) Total with specific date range

[JakeWestonD3]

Hello community,

My company uses irregular fiscal years. They never start or end on the same day each year and they are not always the same amount of days. I want to do cumulative (running) totals that start over each fiscal year. The purpose of this would be a line chart that has the running total for each year (including the YTD running total). I have accomplished this using the "DATESYTD" Function but, as previously mentioned, the fiscal years are inconsistent so the yearly (and monthly) totals are wrong:

1.01 - Cumulative Program Awards - $ - Year-to-Year =

CALCULATE (SUM(Program_Awards_PBI[Program Award $]),

DATESYTD( 'Program_Awards_PBI'[Week Date] ))

running total but incorrect

 
The fiscal years for the company are:

Fiscal Year	Start Date - Fiscal Year	End Date - Fiscal Year
2017	12/31/2016	12/29/2017
2018	12/30/2017	12/28/2018
2019	12/29/2018	12/27/2019
2020	12/28/2019	01/01/2021
2021	1/2/2021	12/31/2021

Looking at fiscal year 2017, you can see that because that fiscal year starts in 2016, the running total is not including the $133,734.44 which is throwing of the total.

My question, how can i do a running total that is between to specific dates? (i.e.: Start date = 12/31/2016, End Date = 12/29/2017). Using the Max function wont work because i dont want to go to the end of the range in my date table.

p.s. the fiscal months are irrgular as well. I have a date table with a consistent (day-to-day) column. In that date table i also have my fiscal years and fiscal months plotted out.

Thank you!

[v-kelly-msft]

Hi @JakeWestonD3 ,

Is your issue solved now？

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[JakeWestonD3]

Hey @v-kelly-msft I do have my date table set up and identified as a date table. The meausre from your post works for the cumulative total calculation and I can make other measures that isolate the years for comparison. The problem is that i need the cumulatives to start over each fiscal year. The "DATESYTD" accomplishes this but it does not start the cumulatives over based on my fiscal years, it does it based on the actual change of the year (December 31st).

[v-kelly-msft]

Hi @JakeWestonD3 ,

Could you pls provide some sample data with expected output for test?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[v-kelly-msft]

Hi  @JakeWestonD3 ,

You need first to create a date table then checking the method in below thread:

https://community.powerbi.com/t5/Desktop/Cumulative-sum-by-month-and-fiscal-year/m-p/556953

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[amitchandak]

@JakeWestonD3 , Create a date tbale and update these start and end date in that table

New columns in date table

Year Start = maxx(filter(FY, Date[Date] >= FY[Start Date] && Date[Date] <= FY[End Date]),FY[Start Date])

Year End = maxx(filter(FY, Date[Date] >= FY[Start Date] && Date[Date] <= FY[End Date]),FY[End Date])

FY Year = maxx(filter(FY, Date[Date] >= FY[Start Date] && Date[Date] <= FY[End Date]),FY[Fiscal Year])

Year Rank = RANKX(all('Date'),'Date'[Year Start],,ASC,Dense)

Day of Year =datediff([Year Start] , [Date],Day) +1

With above columns try new measures for YTD

YTD= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank]) && 'Date'[Day of Year] <= Max('Date'[Day of Year]) ))
LYTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank])-1 && 'Date'[Day of Year] <= Max('Date'[Day of Year])))

or

YTD= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Day of Year] <= Max('Date'[Day of Year]) ))
LYTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Day of Year] <= Max('Date'[Day of Year])))

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 :radacad sqlbi My Video Series Appreciate your Kudos.