cancel
Showing results for
Did you mean:
Frequent Visitor

## Get a set date one year earlier

Hello,

Once again I could use a little help with a specific question. I need to calculate a percentage of recovered patients on one set date one year before the last day of the last completed current month. So as today is March 28th, I will look at the last date of February and than need that date and only that date for last year.

I need this because we want to show percentage of recovered patients for a set moment in time and put the results versus that same set moment in time a year ago, to show whether or not this year we have more recovered patients than we did a year ago at the same set moment. This set moment in time is always the last day of the last completed month, so no flexibility with a date slicer or anything.

This will then show us for example that on 28-02-2022 already 75% of patients for February 2022 and 82% of patients for January 2022 recovered before the end of Feb 2022 (and for Feb 2021 it's 98% of patients at the end of Feb 2022), whereas on 28-02-2021 only 45% of patients for February 2021 and 57% of patients for January recovered before the end of Feb 2021. Does this make any sense?

When I hardcode the date of last year through DATE(2021; 02; 28), the measure (enddate_patient <= DATE(...)) does what I expect it to do and gives me the results for all months prior where the enddate is before the hardcoded date. But when I try to make that DATE flexible depending on the date it uses for the current year, I get errors/failures.

I have tried several things (sameperiodlastyear, Date(Year -1, Month, Day)), but they all give a range of dates for each single month, where I only need the set date one year before the most recent set date, like it is hardcoded.

Is there a way to do this? My feeling says there should be a very simple solution, but for some reason I can't manage to work it out. Hope someone can help me!

1 ACCEPTED SOLUTION
Super User

@GirlPowerBI , You can get that date like

measure  =

var _max= maxx(allselected('Date'), 'Date'[Date])

var _lastyear13 = eomonth(_max,-13) //this will be feb 28,2021

var _lastyear13 = eomonth(_max,-14)+1 //this will be feb 1,2021

return

<Measure calculation >

Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!
2 REPLIES 2
Super User

@GirlPowerBI , You can get that date like

measure  =

var _max= maxx(allselected('Date'), 'Date'[Date])

var _lastyear13 = eomonth(_max,-13) //this will be feb 28,2021

var _lastyear13 = eomonth(_max,-14)+1 //this will be feb 1,2021

return

<Measure calculation >

Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!
Frequent Visitor

Just as easy as I expected it to be, I got stuck in a loophole that I couldn't get out of.

Thanks @amitchandak

Announcements