Forum Discussion

ringovski's avatar
ringovski
Icon for Helper II rankHelper II
4 years ago
Solved

Most Recent Date & Value

Hi All, I am trying to get the most recent date and its assoicated active cases value for each LGA Name in a single table NSW.   I have written the DAX and it gets the most recent date but it just...
Need Help DAX
  • ValtteriN's avatar
    ValtteriN
    4 years ago

    Hi,

    For calculations using most recent date you can use this logic:

    var curdate = max('Calendar'[Date])
    var lastestdate = CALCULATE(MAX('Table'[Date]),ALL('Calendar'),'Table'[Date]<=curdate) return

    CALCULATE([Measure],'Table'[Date]=latestdate)

    I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!






ValtteriN's avatar
ValtteriN
Icon for Community Champion rankCommunity Champion
4 years ago

Hi,

For calculations using most recent date you can use this logic:

var curdate = max('Calendar'[Date])
var lastestdate = CALCULATE(MAX('Table'[Date]),ALL('Calendar'),'Table'[Date]<=curdate) return

CALCULATE([Measure],'Table'[Date]=latestdate)

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!






  • ringovski's avatar
    ringovski
    Icon for Helper II rankHelper II
    4 years ago

    Thanks for the reply, wasn't quite the answer but it put me on the right path. I ended up with;

     

    DAX:

    Recent LGA Cases =
    VAR CurrentLGA = SELECTEDVALUE(NSW[LGA Name])

    VAR MAXXDATE = MAXX(
               FILTER(ALL('NSW'),'NSW'[LGA Name] = CurrentLGA && NSW[active cases] <>0),

                NSW[Date])

    RETURN

    CALCULATE(SUM(NSW[Active Cases]), NSW[Date] = MAXXDate)