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

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