Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
@Anonymous , 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 >
@Anonymous , 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 >
Just as easy as I expected it to be, I got stuck in a loophole that I couldn't get out of.
Thanks @amitchandak
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
21 | |
16 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |