Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Cumulative sum per fiscal year

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

Capture.PNG

 

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

 
1 ACCEPTED SOLUTION

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

8 REPLIES 8
v-lili6-msft
Community Support
Community Support

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
FrankAT
Community Champion
Community Champion

Hi @Anonymous ,

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

 

09-04-_2020_22-27-37.png

 

Regards FrankAT

amitchandak
Super User
Super User

@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
Not applicable

@amitchandak  dosnt work for me.

 

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

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

Greg_Deckler
Super User
Super User

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@Greg_Deckler 

Yes, I can....

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.