Measure to Calculate Last year's total for March , regardless what month and year is chosen

[mgtaylor3]

Hi, Been staring at this for too long and need some help. I Have 2 date slicers... One for Financial Year and One For Financial Month. I need a measure that calculates the total of an amount for the Month of March ( End of Financial Year, month) I chose financial year 2020, the measure needs to look at the previous year each time i choose a financial year. so in summary if I choose April 2020... the measure needs to be for fin year 2019 and fin month march Hope that's clear... any help would be much appreciated. Regards Mike,.

[mgtaylor3 · Accepted solution]

Hi All, while the suggestions replied were much appreciated, they didn't solve the issue, as whenever a new Month or Year was chosen on the page or visual, the " Calc"  then also moved and didn't stay witht he relevant March amount that was required.

I Have though got myself a solution, may not be elegant but it is a workable solution.

On my Revenue_Data Table, I created a Column that used the original Date Column to only give me the March Date that Transaction required... so all financial dates  in the Fin_Year of 2020   got 31/03/2020  ( Fin2020 is April20 to march21)

Once that was done I created a summarised table  of just those March Dates  so I now have a table of all March dates.

I then also created a summarised table of the main data table and filtered it by Fin_Month = 12.

So now I have The main table, a table of march dates and a summarised table of the main table filtered by Month 12 ( March)

I then link these by the table of march dates, by the date field on the main table and summarised table., I also link the summarised to a Enitity table to allow for a list of Countries.

Now  in my visualisations  I can flick between fin years and fin months, while my March date amount  stays steady  so I can now use in a calc..

Excellent.  Might be  along winded way, but has really made this easy to follow and it's a workable solution.

[amitchandak]

@mgtaylor3 , check page 8. It will show data of last march but name will be of this month 

[amitchandak]

@mgtaylor3 , Fin month should be number column , refer the file I shared

[amitchandak]

@mgtaylor3 , My bad , missed -1. Tested on my file

Measure = var _max = maxx(ALLSELECTED('Date'), 'Date'[FY Month]) 
return 
 CALCULATE([Sales],DATESMTD(DATEADD('Date'[Date],-1*_max,MONTH)))

[mgtaylor3]

yesty = var _max = maxx(ALLSELECTED('Calendar'),'Calendar'[FIN_MONTH])

return _max

--CALCULATE([Club_AM],DATESMTD(DATEADD('Calendar'[CALENDAR_DATE],-1*_max,MONTH)))

so What I've put in  to check the code... the _max... simply gives the Month and Year I have chosen in my 2 Splicers... so fin year and Fin Month....   I need it to stay steady on Mar  and the year previous to the one chosen in my slicer...

[mgtaylor3]

Hi There, ok I've tried this and while it gives me figures just noit the correct one... so I'

l'll try and re-iterate a bit clearer.

Financial year 2020 is APRIL2020 to MARCH.2021

So.... I need  a measure that gives me the number of ( in this case opertional clubs) we have in March 2020. An not change  unless the user chooses a different financial year.. ie:  2019   thus I would need March2019 figure.

now that march2019 figure I use against April2019 through to March2020 of the financial year..  and so on and so on..

[mgtaylor3]

Hi There, I'll give that a quick go...

[amitchandak]

@mgtaylor3 , With help from Date table and time intelligence

If you have FY month no (Add +1 if need in _max)

last year last MTD Sales =
var _max = maxx(allselected('Date'), 'Date'[FY Month])
return
CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],_max,MONTH)))

or
last year last MTD Sales =
var _max = datediff(startofyear('Date'[Date],"3/31"), maxx(allselected('Date'), 'Date'[Date]),month)
return
CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],_max,MONTH)))

Creating Financial Calendar - From Any Month
https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calend...

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 :radacad sqlbi My Video Series Appreciate your Kudos.