Calculating the last month of the last fiscal year

[Ski900]

I am having trouble building this calculation. Say for example the user selects April of 2018 in the filters, then it will return the sum from June of 2017, as June is the last month of the fiscal year in my reporting. Basically, I'm will always be calculating the previous June. l have tried using SAMEPERIODLASTYEAR, but have not had success with it. The logic I have below is similar to working logic that I use for previous month calculation. Any help is much appreciated.

[Total Users] = CALCULATE(SUM('mytable'[Number of Users]), FILTER(mytable', mytable'[filter1] = true && mytable'[filter2] <= 28))

Last Month of Last FY =
var currMonth = SELECTEDVALUE('Calendar'[Month])
var currYear = SELECTEDVALUE('Calendar'[Year])

return
IF(
    HASONEVALUE('Calendar'[Month]),
    SUMX(
        FILTER(ALL('Calendar'),
            IF(
                currMonth >= 1 && currMonth <= 6,
                currYear = currYear - 1 && currMonth = 6,
                currYear = currYear && currMonth = 6
                )
            ), [Total Users]           
    ),
    BLANK()
)

[OwenAuger · Accepted solution]

@Ski900

Here's an idea - by combining time intelligence functions you can refer to the last month of the previous year ending June.

Note:

• "2000-06-30" is just a 30-June date in an arbitrary year, see here.
• I'm assuming your date column is 'Calendar'[Date]
• Kept the single month check

Last Month of Last FY =
IF (
    HASONEVALUE ( 'Calendar'[Month] ),
    CALCULATE (
        [Total Users],
        DATESMTD ( PREVIOUSYEAR ( 'Calendar'[Date], "2000-06-30" ) )
    )
)

Regards,

Owen

[Ski900]

It works! Thank you very much!