Sum of Amount for Previous Custom Financial Year

[Adam01]

Hi All!

I am trying to calculate the totals for a custom financial year but for last year

For example the current financial year is 26th of March 2022 to the 25th of March 2023.

So I would need the total for the previous financial year of 26th of March 2021 to the 25th of March 2022

For Current Year I have the following DAX:

Amount YTD = TOTALYTD ( SUM ( 'Sales'[Sales] ), 'Calendar'[Date]. "25/03" )

This returns the Correct value for the current financial year (1.33bn)

However when I do previous financial year:

CALCULATE (

        [Amount YTD],

        SAMEPERIODLASTYEAR ( 'Calendar'[Date] )

    )

It returns (Blank), see below:

I should be expecting to see a value of 1.87bn

I am unsure where to go from here, I have tried PARALLELPERIOD and DATEADD but they return the same result

[Anonymous · Accepted solution]

Hi  @Adam01 ,

I created some data:

Sales:

Calendar:

Here are the steps you can follow：

1. Create measure.

current financial year =
var _today=TODAY()
var _currentdate=DATE(YEAR(_today),3,25)
var _nextdate=DATE(YEAR(_today)+1,MONTH(_currentdate),DAY(_currentdate))
return
SUMX(FILTER(ALL('Calendar'),'Calendar'[Date]>_currentdate&&'Calendar'[Date]<=_nextdate),SUMX(FILTER(ALL('Sales'),'Sales'[Date]='Calendar'[Date]),[Sales]))

previous financial year =
var _today=TODAY()
var _currentdate=DATE(YEAR(_today),3,25)
var _previousdate=DATE(YEAR(_today)-1,MONTH(_currentdate),DAY(_currentdate))
return
SUMX(FILTER(ALL('Calendar'),'Calendar'[Date]>_previousdate&&'Calendar'[Date]<=_currentdate),SUMX(FILTER(ALL('Sales'),'Sales'[Date]='Calendar'[Date]),[Sales]))

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Greg_Deckler]

@Adam01 For custom fiscal calendars, you can pretty much forget about DAX time intelligence functions. Although there is a possible solution using TOTALYTD. You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

Also for a treatment of TOTALYTD using optional parameters for "fiscal year", see this video: