Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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

 

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)
 

 

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??

 

Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

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

1 Reply

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

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