Create measure of max day with data grouped by week

[l_vitorl]

Hi! I'm currently having trouble trying to create a measure that would give me the maximum date of a given week where a table has data entries.

Here's a sample of the data

Num vehic	date	weekday
43	02/02/2023	5
135	03/02/2023	6
258	04/02/2023	7
86	05/02/2023	1
245	06/02/2023	2
206	07/02/2023	3
148	08/02/2023	4
77	09/02/2023	5
161	13/02/2023	2
257	14/02/2023	3
6	15/02/2023	4
33	16/02/2023	5
252	17/02/2023	6
221	19/02/2023	1
34	20/02/2023	2
161	21/02/2023	3

As you can see, some weeks have data entries on all seven days, while others don't. My expected result is something like this

Num vehic	date	weekday	max_dayofweek_with_data
43	02/02/2023	5	04/02/2023
135	03/02/2023	6	04/02/2023
258	04/02/2023	7	04/02/2023
86	05/02/2023	1	09/02/2023
245	06/02/2023	2	09/02/2023
206	07/02/2023	3	09/02/2023
148	08/02/2023	4	09/02/2023
77	09/02/2023	5	09/02/2023
161	13/02/2023	2	17/02/2023
257	14/02/2023	3	17/02/2023
6	15/02/2023	4	17/02/2023
33	16/02/2023	5	17/02/2023
252	17/02/2023	6	17/02/2023
221	19/02/2023	1	21/02/2023
34	20/02/2023	2	21/02/2023
161	21/02/2023	3	21/02/2023

I've actually solved my problem by creating a summarized related table, but in my opinion its rather "ugly" so I was wondering if a more simple and elegant solution with a measure could be achieved. Any ideas?

Thanks in advance

[johnt75 · Accepted solution]

You'll need a week commencing column in your date table, and you'll need to use the date column from the date table in your visual, not the date column from the fact table. Then you can create a measure like

Max day of week with data =
VAR CurrentWeek =
    SELECTEDVALUE ( 'Date'[Week commencing] )
VAR MaxDateWithData =
    CALCULATE (
        MAX ( 'Fact Table'[Date] ),
        REMOVEFILTERS ( 'Date' ),
        'Date'[Week commencing] = CurrentWeek
    )
RETURN
    MaxDateWithData

[l_vitorl]

Thanks!

Had to adapt a few things to account for other columns that needed to be filtered in the visual (which I ommited in the sample data), but it worked.

For some reason the REMOVEFILTERS wasn't needed, not sure why. I will test a few scenarios to check for some unexpected behaviour but all in all it worked.

Here's the adapted formula, in case someone needs

maxdaywithdata =

VAR numweekyear =
WEEKNUM(fact_vehicle_dates[date])

VAR client =
fact_vehicle_dates[CD_CLIENT]

VAR MaxDayOnWeekNum =
    CALCULATE (
        MAX(fact_vehicle_dates[weekday]),
        FILTER( fact_vehicle_dates, WEEKNUM(fact_vehicle_dates[date]) = numweekyear),
        FILTER( fact_vehicle_dates, fact_vehicle_dates[CD_CLIENT] = client)
    )
RETURN
    MaxDayOnWeekNum