Cumulative sum per fiscal year

[Anonymous]

Guys,

I need to provide a column with the cumulative sum per fiscal year, like table below.

Fiscal Year start month: september

FIscal Year and month: august

When I use the formula: calculate(sum(revenue),Filter(ALL(Table); period<=MAX(period)) the result is that the column "cumulative revenue" sum all months in row and not only betwen month 1 and 12 and restart the sum in first month of new fiscal year...

Many thanks for helping me....

[v-lili6-msft]

hi  @Anonymous 

For your case, you could just add a Fiscal Year column, then use this formula to create a measure:

calculate(sum(revenue),Filter(ALL(Table); [Fiscal Year Month]<=MAX([Fiscal Year Month])&& [Fiscal Year Column] =MAX([Fiscal Year Column]) ))

Regards,

Lin

[FrankAT]

Hi @Anonymous ,

I have changed the name of the period, see figure:

Regards FrankAT

[amitchandak]

@Anonymous 

Datesytd and totalytd can give you that , but for that you need a date column. If you do not have then create a column like this

New column

Date = "01-" & [Period] // Based on your month Format. Change data type to date

Use with end of year ar 8/31

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"8/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/

[Anonymous]

@amitchandak  dosnt work for me.

the result was only the revenue of the month in the row....

[amitchandak]

@Anonymous , If not resolved by above solutions, share the sample data and mark all members supporting on this @

[Greg_Deckler]

Do you have or can you add a Fiscal Year column?

[Anonymous]

@Greg_Deckler 

Yes, I can....

[Greg_Deckler · Accepted solution]

calculate(sum([revenue]),Filter(ALL(Table); [period]<=MAX([period]) && [fiscalyear] = MAX([fiscalyear]))