topic Re: DAX measure to fill the gaps in DAX Commands and Tips

DAX measure to fill the gaps

nijos — Mon, 11 Mar 2024 17:15:25 GMT

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.

Re: DAX measure to fill the gaps

Greg_Deckler — Mon, 11 Mar 2024 18:15:50 GMT

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

Re: DAX measure to fill the gaps

nijos — Mon, 11 Mar 2024 18:32:04 GMT

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

Re: DAX measure to fill the gaps

Greg_Deckler — Mon, 11 Mar 2024 18:38:11 GMT

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