DAX measure to fill the gaps

[nijos]

I have created a pbix with three tables:

Actuals, Target and a Date table.

The actuals table has values for all 31 days of March 2024.

The target table only has values for each Friday of March 2024.

I want to create a measure that gives me the Target value to fill the gaps in an additional column of my matrix visual.

So, for each day that is not a Friday, I want to get the value of that weeks Friday value.

I know how to create additional rows for in the Target query via Power Query with List.Dates but I am searching for a DAX calculated measure solution (not a calculated column).

I have also created an example PBIX for my query.

https://drive.google.com/file/d/1cQmlY0XXtThw0uIIo5RdVLlUAolRtwIE/view?usp=sharing

Thank you in advance for your help for creating the correct DAX formula.

[nijos]

Thank you @Greg_Deckler , I had the logic in my head all afternoon but could not get it in the measure myself.
Added ReturnType 21 to the WEEKNUM function and now it works perfect!

Max Target fixed = 
    VAR __Date = MAX( 'Date'[Date] )
    VAR __Week = WEEKNUM( __Date, 21 )
    VAR __Year = YEAR( __Date )
    VAR __Target = MAXX( FILTER( ALL( 'Targets' ), YEAR( [Date] ) = __Year && WEEKNUM( [Date], 21 ) = __Week ), [Target] )
RETURN
    __Target

[Greg_Deckler]

@nijos Perfect. Yeah, I wasn't sure which week number format you were using so I just went with the default.

[Greg_Deckler · Accepted solution]

@nijos Try this:

Measure = 
    VAR __Date = MAX( 'Date'[Date] )
    VAR __Week = WEEKNUM( __Date )
    VAR __Year = YEAR( __Date )
    VAR __Target = MAXX( FILTER( ALL( 'Targets' ), YEAR( [Date] ) = __Year && WEEKNUM( [Date] ) = __Week ), [Target] )
RETURN
    __Target