Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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....
Solved! Go to Solution.
calculate(sum([revenue]),Filter(ALL(Table); [period]<=MAX([period]) && [fiscalyear] = MAX([fiscalyear]))
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
Hi @Anonymous ,
I have changed the name of the period, see figure:
Regards FrankAT
@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 , If not resolved by above solutions, share the sample data and mark all members supporting on this @
calculate(sum([revenue]),Filter(ALL(Table); [period]<=MAX([period]) && [fiscalyear] = MAX([fiscalyear]))
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |