Forum Discussion

anandsoftweb's avatar
anandsoftweb
Advocate V
9 years ago
Solved

DAX expression for Max Date data display in Table

Hello All,   I am looking for MAX date from Date column and based on that output table will be display on max date   Yes and No is Two filter slicer   Below is the sample data   ID Name ...
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi anandsoftweb

     

    From the description of the problem you want to find the max date for a given month and year from the data table.

     

    1. Create a calculated column YearMonth = Year(dataTable[Date]) * 100 = Month (dataTable[Date])

     

    2. Create another caculated column

         MaxDate = CALCULATE(MAX(dataTable[Date]),FILTER(dataTable,[YearMonth]=EARLIER(dataTable[YearMonth])))

     

    3. Now plot and you will get your desired output.  Sample output based on the data.

     

     

    If this solves your issue, please accept it as a solution and also give KUDOS.

     

    Cheers

     

    CheenuSing

     

v-qiuyu-msft's avatar
v-qiuyu-msft
Community Support
9 years ago

Hi anandsoftweb,

 

You can create a calculated column to decide which row  is max date for each Availability group :

 

MaxDateEachAvailability = IF('Table2'[Date]=CALCULATE(MAX('Table2'[Date]),ALLEXCEPT(Table2,'Table2'[Availability])),1,0)

 

Then add this column to the table visual filter, check value 1. Please take a look at attached .pbix file.

 

 

Best Regards,
Qiuyun Yu

  • anandsoftweb's avatar
    anandsoftweb
    Advocate V
    9 years ago

    v-qiuyu-msft

     

    Thanks for your reply.

     

    Here there are little concern if we apply other filters it will not giving the correct result but can you help me to create dax for calculate column in which it gives the max date of this month from date column data

     

    Like in our case date is source column and in that if we want to find out max date as below

     

    IDNameAvailabilityDateCalculate column with DAX
    1ABCyes16/3/201716/3/2017
    1DEFno16/3/201716/3/2017
    2DEFyes16/3/201716/3/2017
    3OPQyes15/3/201716/3/2017
    3OPQyes11/3/201716/3/2017
    4RSTyes15/2/201715/2/2017
    5UVWno14/2/201715/2/2017
    6XYZno10/3/201716/3/2017
    6XYZno13/1/201716/1/2017
    7SDFyes13/3/201716/3/2017
    8SDFyes11/2/201715/2/2017
    9LMNno16/1/201716/1/2017

     

    So this might solve our issue

     

    I really appreciate & Thanks in advance :)

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Hi anandsoftweb

       

      From the description of the problem you want to find the max date for a given month and year from the data table.

       

      1. Create a calculated column YearMonth = Year(dataTable[Date]) * 100 = Month (dataTable[Date])

       

      2. Create another caculated column

           MaxDate = CALCULATE(MAX(dataTable[Date]),FILTER(dataTable,[YearMonth]=EARLIER(dataTable[YearMonth])))

       

      3. Now plot and you will get your desired output.  Sample output based on the data.

       

       

      If this solves your issue, please accept it as a solution and also give KUDOS.

       

      Cheers

       

      CheenuSing

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        I too want a similar output, wherein the table while refreshing everyday it should show the Max (Table[Day]) and its related values only in the Table Chart. Also I have a Slicer with same Day column, while we make selections values in chart should change accordingly. Can someone tell me how should I solve this?