Rolling 13 financial months

[JimJim]

Hi Guys,

I have attached a pbix file with a date table included. I am trying (and failing) to calculate a column that will let me know if the date is within the previous 13 months (but not including the current month). This sounds easy, but the reason I am struggling is because I need to count financial months and usually dates from one month fall into another financial month, as an example, 25th Oct 2020 - 31st Oct 2020 fall within the financial month of November 2020.

On the pbix file, there is a column ExpectedResults, this is the value I am trying to calculate in the IsRolling13M column

Please help

dates.pbix 

[Vera_33 · Accepted solution]

@JimJim 

My bad, change the HelpColumn logic to this

HelpColumn = [FinancialYear]*12+[FinancialPeriod]

[JimJim · Accepted solution]

I managed to work it out, thank you to @Vera_33 for setting me on the right track.

It's a bit of a hack but the following code does the job

IsRolling13M = 
VAR _month = MONTH(TODAY())
VAR _year = YEAR(TODAY())
RETURN
SWITCH(TRUE(),
    // any month in the same financial year will be within 13 months
    'Time'[FinancialYear] = _year && 'Time'[FinancialPeriod] < _month, TRUE(),
    // any month in the previous year which is greater than or equal to the current month will also be within 13 months
    'Time'[FinancialYear] = _year-1 && 'Time'[FinancialPeriod] >= _month, TRUE(),
    // finally, if the current month is January we need to include December from 2 years previous
    'Time'[FinancialYear] = _year-2 && _month = 1 && 'Time'[FinancialYear] = 12, TRUE(),
    FALSE())

[Vera_33]

Hi @JimJim 

In your Date table, all the FinancialYear, period...columns are already there? the only one is Rolling13M? Added a helper column, file attached. Let me know if you are looking for something else.

[JimJim]

Thank you for your reply, but this doesn't work. November and December 2020 should be included in the Rolling 13 months but as you can see, your solution doesn't include any YEARS before the previous one

[Vera_33 · Accepted solution]

@JimJim 

My bad, change the HelpColumn logic to this

HelpColumn = [FinancialYear]*12+[FinancialPeriod]

[JimJim]

Thank you so much, this is much better than my solution