cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper II

## 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

1 ACCEPTED SOLUTION
Community Champion

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

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
5 REPLIES 5
Community Support

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

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper II

@v-qiuyu-msft

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 🙂

Community Champion

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

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Anonymous
Not applicable

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?

Helper II

@v-qiuyu-msft

Does it possible to make it ?

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors