Forum Discussion

DH_RF's avatar
DH_RF
Regular Visitor
2 years ago

Measure for MAX date or LASTDATE

First time posting so please be nice.

 

I've tried several MAX date solutions but none seem to work.  In concept, it is the same as many posts on here with successful solutions.  I do not have access to Editor so this must done through a measure.

Listed is a table of Equipment that spans over several years.  Each time a Equipment Serial number's characteristics is changed through CRM, it creates another active row.  I'm looking for a general list of equipment by site, serial number, etc.  The only was to do this is to look at the last transaction date for each piece of equipment by equipment serial number.  So in theory, create a measure that will pull all the column data by looking at the MAX date or LASTDATE of each equipment serial number.  The most recent date has all of the correct column date.  Not every piece of equipment will be updated with new information every month else I could just list the most recent year and month.  One other kicker is the date column might not be formated as a date and it is not in the same "Equipment" dataset, but is is a 1 to 1 relationship.  I'm unable to change the format unless it is through a measure.  Tried, permissions restricted.

 

Here is my attempt #8.

 

Last Date =
CALCULATE(FIRSTNONBLANK(Equipment[Equipment Serial],1),
FILTER('Date','Date'[Date]= MAX('Date'[Date])))
 
Everything falls under Equipment except the Date.
 
Any help would be greatly appreciated.
 
DH RF
Need Help

10 Replies

  • JamesFR06's avatar
    JamesFR06
    Icon for Resolver IV rankResolver IV
    2 years ago

    HI

     

    Last Date =
    var EqSerial=selectedvalue(Equipment[Equipment Serial])
    var MaxDate=calculate(MAX('Date'[Date]),Equipment[Equipment Serial]=Eqserial
    Var Datevalue=selectedvalue('Date'[Date])
    Var result= if(MaxDate=Datevalue,1)
    return
    result
     
    and after you put this measure on the filter panel and value = 1
    and you should have only requiered records
    • DH_RF's avatar
      DH_RF
      Regular Visitor
      2 years ago

      No errors, but unfortunately nothing changed in the results.  The measure is in a general decimal format.  Does this need to change?  I would think not since the measure is returning a 1 (yes).

       

       

      Thank you,

       

      DH RF

  • JamesFR06's avatar
    JamesFR06
    Icon for Resolver IV rankResolver IV
    2 years ago

    Hi 

     

    I forgot all on the Maxdate calulation so ==> var MaxDate=calculate(MAX('Date'[Date]),Equipment[Equipment Serial]=Eqserial,all(Equipement))

    Try like this please 

    • DH_RF's avatar
      DH_RF
      Regular Visitor
      2 years ago

      Updated, no results (blank).  Regardless, I appreciate the help!

       

       

      DH RF

  • DH_RF's avatar
    DH_RF
    Regular Visitor
    2 years ago

    James,

    Were you able to review my response?  Anyone able to help me solve the issue?  Else I just exported the data to Excel and filtered and sorted the data until I listed the most recent date record.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi, DH_RF 

      Thank you very much JamesFR06 . I think you can try the following DAX expression:

      Last Date =
CALCULATE (
    MAX ( 'Date'[Date] ),
    ALLEXCEPT ( Equipment, Equipment[Equipment Serial] )
)

       

       

       

      How to Get Your Question Answered Quickly

      If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

      Best Regards

      Jianpeng Li

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      • DH_RF's avatar
        DH_RF
        Regular Visitor
        2 years ago

        Unfortunately, when adding the measure, the system times out based on reached max resources.  Here is a link to the file:

        HiLo Dashboards (5).pbix

         

        The ouput is displaying only the most recent dated record located on the right for each equipment serial.

        DH_RF