Creating a custom year between two dates for a measure, e.g. 12 August 2019 to 12 August 2020

[Bemused]

Hi all,

I've tried to search for a solution to this, but I can't find one--however I think this is more of a case that I'm not sure of the terms I should be searching for.  Any links would be helpful to existing posts.

So I have a measure like this: 

Nth Year of employment = DATEDIFF(SUM('Employment Data'[Commencement Date]), DATE(2020,12,31),YEAR)+1

Where the DATE() is the latest date contained in the dataset.

And that works absolutely fine, because Power BI is designed to see an year as 1 January YYYY to 31 December YYYY.

However, if the dataset ends at an odd date, for example 14 May 2021, and I create the same measure:

Nth Year of employment = DATEDIFF(SUM('Employment Data'[Commencement Date]), DATE(2021,05,14),YEAR)+1

The results are considerably off kilter, because Power BI still sees an year as starting on 1 January, as opposed to 14 May 2020 to 14 May 2021 as should be the case.

I've resorted to going to the raw data and creating a column in Excel with this formula to get the right results:

=DATEDIF(Cellref,DATE(2021,5,14), "y")

Sorry that's a bit convoluted, but how do you make this work in Power BI?

[amitchandak · Accepted solution]

@Bemused , Based on initial Description

Means from

12 August 2019 to 11 August 2020

Calendar = calendar(Date(2019,08,12),Date(2020,08,11))  // New calendar table 

Start of year = Startofyear([Date], "08/11")

Endof year = Endofyear([Date], "08/11")

Start of month = eomonth([Date], -1) +12

End of month = eomonth([Date], 0) +11 

Based on Start date month, year , you can create Rank and use that for Current Vs prior

Year Rank = RANKX(all('Date'),'Date'[Year Start date],,ASC,Dense)

Measure
This Year = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),h'Date'[Year Rank]=max('Date'[Year Rank])))
Last Year = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank])-1))

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA