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

Question

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.&nbsp; Any links would be helpful to existing posts.&nbsp;So I have a measure like this:&nbsp;Nth Year of employment = DATEDIFF(SUM('Employment Data'[Commencement Date]), DATE(2020,12,31),YEAR)+1Where the DATE() is the latest date contained in the dataset.&nbsp;And that works absolutely fine, because Power BI is designed to see an year as 1 January YYYY to 31 December YYYY.&nbsp;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&nbsp;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.&nbsp;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")&nbsp;Sorry that's a bit convoluted, but how do you make this work in Power BI?&nbsp;&nbsp;

amitchandak · Accepted Answer

Bemused , Based on initial Description 
Means from
 12 August 2019 to 11 August 2020 
&nbsp;
Calendar = calendar(Date(2019,08,12),Date(2020,08,11))&nbsp; // New calendar table&nbsp;
&nbsp;
Start of year = Startofyear([Date], "08/11")
Endof year = Endofyear([Date], "08/11")
&nbsp;
Start of month = eomonth([Date], -1) +12
End of month = eomonth([Date], 0) +11&nbsp;
&nbsp;
&nbsp;
Based on Start date month, year , you can create Rank and use that for Current Vs prior
&nbsp;
Year Rank = RANKX(all('Date'),'Date'[Year Start date],,ASC,Dense)
&nbsp;
&nbsp;
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))
&nbsp;
Power BI — Year on Year with or Without Time Intelligence https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38ahttps://www.youtube.com/watch?v=km41KfM_0uA
&nbsp;
Power BI — Month on Month with or Without Time Intelligence https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090ehttps://www.youtube.com/watch?v=6LUBbvcxtKA

Forum Discussion

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

12 August 2019 to 11 August 2020

1 Reply

12 August 2019 to 11 August 2020

Related content

Need help creating a measure

Help to create DAX Measure

Create count measure

Creating new measure, only showing other measures

Create Measure to Return Highest Value