Measure "Tickets resolved last year to date" not working correctly

[ThoSt]

Hi all,

I have a little bit of an issue with the measure I created and hope you can help me. I hope I manage to describe this well 😉

I have a date field "Resolution Date" which is also used as a slicer on the reports page.

On that page I have a KPI visualization which counts all tickets resolved in the selected time frame (Indicator).

The trend axis (hidden) is [Resolution Date].Year.

The target goal is set to the measure "Tickets Resolved Last Year To Date" which I created.

The measure looks like this:

Tickets Resolved Last Year to Date = CALCULATE(DISTINCTCOUNT(Tickets[Key]),FILTER(ALL(Tickets),Tickets[Resolution Date].[Year]=YEAR(TODAY()-365) && Tickets[Resolution Date] <= TODAY()-365))

Now, this worked fine until the end of 2020. With the new year starting and selecting 2020 as my "Resolution Date" slicer the measure is obviously not correct as it does now only go back to 2020 and not 2020 as it should.

I've tried to replace TODAY() with LASTDATE(Tickets[Resolution Date]) but this returns blank. Logically this would be the date I am after to calculate the measure, but I am not sure how the syntax should be.

Thanks for your help!

ThoSt

[v-kelly-msft]

Hi @ThoSt ,

You'd better create a date dim table,then use time intelligence function as mentioned by @amitchandak ,if you still feel frustrated ,provide some sample data,I would write the formula for you.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[amitchandak · Accepted solution]

@ThoSt , Try with a date table and time intelligence

LYTD = CALCULATE(DISTINCTCOUNT(Tickets[Key]),DATESYTD(dateadd('Date'[Date],-1,year))

LYTD QTY forced=
var _max = date(year(today())-1,month(today()),day(today()))
return
if(max('Date'[Date])<=_max, CALCULATE(DISTINCTCOUNT(Tickets[Key]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max), blank())
//OR
//CALCULATE(DISTINCTCOUNT(Tickets[Key]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
//TOTALYTD(DISTINCTCOUNT(Tickets[Key]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)

Refer my video why TI does not work

https://www.youtube.com/watch?v=OBf0rjpp5Hw

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

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.