Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

DAX - need to return a value from a calculated column from between 2 dates

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: 

Most Recent Audit = CALCULATE(LASTDATE(Table[Date Of Last Audit]),ALLEXCEPT(Table, Table[Location]))

 

PhoenixBird_0-1618489258930.png

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)

Reporting Month minus 2 years = EOMONTH([Today],-26)
 
PhoenixBird_1-1618490356540.png

 

So essentially I want to return the value from the [Most Recent Audit] column, but only where it falls between the Reporting Month and Reporting Month minus 2 years dates.
 
I'm stuck! Any ideas?
Perhaps the Reporting Month and Reporting Month Minus 2 years should be columns instead of measures??

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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 ))

View solution in original post

1 REPLY 1
amitchandak
Super User
Super User

@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 ))

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.