cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PhoenixBird
Helper I
Helper I

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 IV
Super User IV

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

1 REPLY 1
amitchandak
Super User IV
Super User IV

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors