Forum Discussion

joep78's avatar
joep78
Helper III
6 years ago
Solved

MAX value date field

Hi all,

 

I'm looking for a solution to get the MAX value of a date field but based on another value of another column. hereby a snapshot of the table with only the relavant column:

 

SyscodeNameContract end dateContract code
19Automatic renewal +1 yearmaandag 31 juli 202866
18Renewal +5 yearszaterdag 31 juli 203266
21Renewal +5 yearsvrijdag 14 september 202968
22Automatic renewal +1 yearzondag 14 september 202568

 

I have a contract code (66 & 68) and both have to renewal options. 1 for 1 year, the other for 5 yrs. I'm only interested in the option with the highest contract end date. This count ofcourse per each contract. Does anybody have a solution for this. I was not able to figure this one out with MAX/FILTER or any other solution.

 

Thanks in advance for support.

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Joe - 

     

    Is this what you are after (please see attached image). It is a matrix visual btw, and it has only the max date values you are after. It is a measure created and I used it in the matrix visual. If you confirm that is what you are after, I will be able to share the measure as well.

     

     

     

     

     

    Regards

    Eswar

     

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Try like

    calculate(count([Syscode]),filter(all(table),table[Contract end date]=max(table[Contract end date])))

     

    prefer to join Contract end date with the date of date dimension and try

     

    calculate(count([Syscode]),filter(all(date),date[date]=max(date[date])))

     

    Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
    Thanks. My Recent Blog -
    Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
    Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

    Connect on Linkedin

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Joe - 

     

    Is this what you are after (please see attached image). It is a matrix visual btw, and it has only the max date values you are after. It is a measure created and I used it in the matrix visual. If you confirm that is what you are after, I will be able to share the measure as well.

     

     

     

     

     

    Regards

    Eswar

     

    • joep78's avatar
      joep78
      Helper III
      6 years ago

      Hi Eswar,

       

      I think this is the solution i'm looking for with the only diference that for contract 68 the max year should be  Friday 14 september 2029. 

       

      IS it possible to share the meassure so i'm able to test this. thanks in advance for your reply.

       

      regards, Joep

  • v-xuding-msft's avatar
    v-xuding-msft
    Community Support
    6 years ago

    Hi joep78 ,

    If you use FILTER('Table','Table'[Contract end date] = MAX('Table'[Contract end date])) , the result is from the highest contract end date. How do you calculate it? Can you please share your formulas and show the expected results?  Then we will understand clearly and solve it quickly.

     

    Best Regards,

    Xue Ding

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