Create a new column with DAX to display latest date within another category

[AngelaB]

Hello

I am very new to PowerBI so I apologise if the language I'm using doesn't align with the correct Power BI terms and this question may be very basic. I come from a research background and am more familiar with data management from a statistical analysis point of view.

I have data that is filtered by site and grouped timepoint that I want to summarise in a visualisation. The trouble that I'm having is that rather than displaying Timepoint on my visualisation (see image below), I want to display the latest date that the data within that timepoint was collected. From my reading I suspect that I need to create a new column using DAX to do this (maybe using multipe if/return statements?) but can't work out how to write the command.

Here is the example data:

Site	Timepoint	Date
1	1	8/08/2023
1	1	8/08/2023
1	1	28/08/2023
1	2	13/09/2022
1	2	11/09/2022
1	2	5/09/2022
2	1	27/01/2022
2	1	20/02/2022
2	1	1/03/2022
2	2	15/09/2023
2	2	12/09/2023
2	2	11/09/2023

Here is what I *think* I need to solve my visualisation problem:

Site	Timepoint	Date	Latest Date
1	1	8/08/2023	28/08/2023
1	1	8/08/2023	28/08/2023
1	1	28/08/2023	28/08/2023
1	2	13/09/2022	13/09/2023
1	2	11/09/2022	13/09/2023
1	2	5/09/2022	13/09/2023
2	1	27/01/2022	1/03/2023
2	1	20/02/2022	1/03/2023
2	1	1/03/2022	1/03/2023
2	2	15/09/2023	15/09/2023
2	2	12/09/2023	15/09/2023
2	2	11/09/2023	15/09/2023

Any advice about writing this DAX or alternative solutions would be very much appreciated!

[DataNinja777 · Accepted solution]

Hi AngelaB,

I created the same output in a calculated column as shown in your second table using dax as shown below:

Prior to that, I created the calculated calendar table and created a relationship between the dimension table and the fact table. 

I attach the link to the pbix file below:

latest date within another category.pbix

[AngelaB]

Thank you so much and for such a quick reply! That has worked perfectly. Thank you also for the .pbix file; Your explanation was clear, but having the file made it very simple to follow.

What a great community 🙂