Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Max Date Count

Hello,

 

I am pretty new to power bi and would like to calculate the sum of the max date in the below table

Product IdDate
17/21/2021 18:49
27/20/2021 18:49
37/19/2021 18:49
47/20/2021 18:49
57/21/2021 18:49
67/22/2021 18:49
77/22/2021 18:49
87/22/2021 18:49
97/22/2021 18:49
107/22/2021 18:49

 

In this case it is going to be 5. Could you please help me with the DAX.

Thanks
AD

4 Replies

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

    Hi Anonymous ,

     

    First, you need to find the Max, so you can write the below code to find the Max :

    VAR _MaxDate = MAX ( 'Product'[Date] )
    then use this VAR to calculate the sum of the max date (Count):
    CALCULATE ( COUNTA ( 'Product'[Date] ), 'Product'[Date] = _MaxDate )
     
     
    So the Measure is as below:
    (make sure the table and column names are aligned with your file, then Copy and paste that on your file)
     
    Sum of the Max =
    VAR _MaxDate =
    MAX ( 'Product'[Date] )
    RETURN
    CALCULATE ( COUNTA ( 'Product'[Date] ), 'Product'[Date] = _MaxDate )
  • Samarth_18's avatar
    Samarth_18
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hi Anonymous ,

     

    You can try below code:-

    count_of_dates = 
CALCULATE ( COUNT ( 'Product_data'[Date] ), LASTDATE('Product_data'[Date]) )

    output:-

    Thanks

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thank you so much. That worked. One last question. if I use the similar table with few more information :

      ProductDateNameADDRESS
      17/21/2021 18:49Aabc
      27/20/2021 18:49Bdef
      37/19/2021 18:49Cdhk
      47/20/2021 18:49Dscj
      57/21/2021 18:49Esbn
      67/22/2021 18:49Fsjx
      77/22/2021 18:49Gscn
      87/22/2021 18:49Hivd
      97/22/2021 18:49Iskl
      107/22/2021 18:49Jkjs

       

      Now I want to create a table with product, name and address using the max date, how do I do that?

      • Samarth_18's avatar
        Samarth_18
        Icon for Community Champion rankCommunity Champion
        5 years ago

        Hi Anonymous 

         

        You can directly drag your fields on table visual and take a latest of your date. PFB screenshot for reference:-

         

        Note:- You can get latest option when you right click on Date in Values Pane