cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GirlPowerBI
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
amitchandak
Super User
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 !!

View solution in original post

2 REPLIES 2
amitchandak
Super User
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 !!

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

 

Thanks @amitchandak 

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors