DAX expression for Max Date data display in Table

[anandsoftweb]

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

[v-qiuyu-msft]

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]

@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

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 🙂

[CheenuSing · Accepted solution]

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]

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?

[anandsoftweb]

@v-qiuyu-msft

Does it possible to make it ?