Show sum for last month in financial year

[Andrea_Jess]

Hi All, 

I need to return the forecasted turnover amount for the last month in the financial year. 

Our financial year ends 06/30 each year and i basically need to just return the sum of forecasted turnover headcount number at June but it should be dynamic. I.e. it automatically detects June as being the last month in the fiscal year. 

it should only sum for the month of june. Any ideas on how i can do this? I can't seem to limit the aggregation to just June. 

[amitchandak]

@Andrea_Jess , Try a measure like

last year last month =
var _m = -1* if(month(Today()) >6 , month(Today()) -6, month(Today())+6)
return
CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],_m,MONTH)))

[Andrea_Jess]

Hi Amit, 

Doesn't quite work for me. I get blank unless i select full FY. 

We track full 12 month period within each month. For example, for Jul21 fiscal month, we track how the full year looks like from Jul21-Jun22. Reason is because our spend allocations change monthly so this allows us to have historical view. 

Im trying to get it so that when a user select a fiscal month, it looks at the fiscal dates (so the 12 fiscal dates within each fiscal month) and it automatically returns sum of amount or MTD view for Jun e only . 

[v-jingzhang]

Hi @Andrea_Jess 

Can you provide some sample data and expected output? Currently I don't understand what is the 12 fiscal dates within each fiscal month? And how it should automatically change?

If you want to calculate the sum of the whole June (from 1st June to 30th June) in the current fiscal year, you could try below measure. 

Total =
VAR vYearEnd =
    IF (
        MONTH ( TODAY () ) > 6,
        DATE ( YEAR ( TODAY () ) + 1, 6, 30 ),
        DATE ( YEAR ( TODAY () ), 6, 30 )
    )
RETURN
    CALCULATE (
        SUM ( 'table'[Amount] ),
        ALL ( 'table'[date] ),
        DATESBETWEEN ( 'table'[date], vYearEnd - 29, vYearEnd )
    )

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.