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.
Please can somebody help with this, been struggling for days and I cant get my head around it. Our company runs a report 2 months behind. So when it's April, they're looking at February's data.
I have some data that looks like the picture below. The first 3 columns are straight out of the data source.
The fourth column ('Most Recent Audit') is a calculated column I created to give me the most recent audit date per location (regardless of reporting period).
The DAX looks like this:
I now need to create a new column that returns the value in the [Most Recent Audit] column, but only where
the Reporting Period is less than or equal to 28th Feb 2021 (The last day of the month which was 2 months ago) AND is more than 28th Feb 2019 (the last day of the month which was 2 years & 2 months ago).
I have already created measures for these 2 dates:
Reporting Month = EOMONTH([Today],-2)
Solved! Go to Solution.
@Anonymous , Based on what I got. Try new measure
Most Recent Audit =
Var _max = EOMONTH([Today],-2)
Var _min = EOMONTH([Today],-26)
return
CALCULATE(max(Table[Date Of Last Audit]),filter(allselected(Table), Table[Location] = max(Table[Location]) && Table[Date Of Last Audit] >=_min && Table[Date Of Last Audit] <=_max ))
New column
Most Recent Audit =
Var _max = EOMONTH([Today],-2)
Var _min = EOMONTH([Today],-26)
return
CALCULATE(max(Table[Date Of Last Audit]),filter((Table), Table[Location] = earlier(Table[Location]) && Table[Date Of Last Audit] >=_min && Table[Date Of Last Audit] <=_max ))
@Anonymous , Based on what I got. Try new measure
Most Recent Audit =
Var _max = EOMONTH([Today],-2)
Var _min = EOMONTH([Today],-26)
return
CALCULATE(max(Table[Date Of Last Audit]),filter(allselected(Table), Table[Location] = max(Table[Location]) && Table[Date Of Last Audit] >=_min && Table[Date Of Last Audit] <=_max ))
New column
Most Recent Audit =
Var _max = EOMONTH([Today],-2)
Var _min = EOMONTH([Today],-26)
return
CALCULATE(max(Table[Date Of Last Audit]),filter((Table), Table[Location] = earlier(Table[Location]) && Table[Date Of Last Audit] >=_min && Table[Date Of Last Audit] <=_max ))
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |