Forum Discussion
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 | Availability | Date |
| 1 | ABC | yes | 16/3/2017 |
| 1 | DEF | no | 16/3/2017 |
| 2 | DEF | yes | 16/3/2017 |
| 3 | OPQ | yes | 15/3/2017 |
| 3 | OPQ | yes | 11/3/2017 |
| 4 | RST | yes | 15/3/2017 |
| 5 | UVW | no | 14/3/2017 |
| 6 | XYZ | no | 16/3/2017 |
| 6 | XYZ | no | 13/3/2017 |
| 7 | SDF | yes | 13/3/2017 |
| 8 | SDF | yes | 11/3/2017 |
| 9 | LMN | no | 16/3/2017 |
| Output in table If User Select = yes | |||
| 1 | ABC | yes | 16/3/2017 |
| 2 | DEF | yes | 16/3/2017 |
| Output in table If User Select = no | |||
| 1 | DEF | no | 16/3/2017 |
| 6 | XYZ | no | 16/3/2017 |
| 9 | LMN | no | 16/3/2017 |
Let me know any thing else needed
Thanks
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
6 Replies
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 YuThanks 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
ID Name Availability Date Calculate column with DAX 1 ABC yes 16/3/2017 16/3/2017 1 DEF no 16/3/2017 16/3/2017 2 DEF yes 16/3/2017 16/3/2017 3 OPQ yes 15/3/2017 16/3/2017 3 OPQ yes 11/3/2017 16/3/2017 4 RST yes 15/2/2017 15/2/2017 5 UVW no 14/2/2017 15/2/2017 6 XYZ no 10/3/2017 16/3/2017 6 XYZ no 13/1/2017 16/1/2017 7 SDF yes 13/3/2017 16/3/2017 8 SDF yes 11/2/2017 15/2/2017 9 LMN no 16/1/2017 16/1/2017 So this might solve our issue
I really appreciate & Thanks in advance :)
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
