Missing values per week - return latest active value

Hi Anonymous 

If you have a large number of weeks, you may need to use dax to build a separate week table.

And  amitchandak 's measure works well if you build relationships between two tables.

You can try my measure if you don't want to build relationships between two tables.

Firstly, we add a WeekNum column in Price Table.

WeekNum = SUBSTITUTE('Price'[Week],"Week ","")

Change the column type from text to whole number.

Then build Allweek Table.

AllWeek = ADDCOLUMNS(GENERATESERIES(MIN('Price'[WeekNum]),MAX('Price'[WeekNum]),1),"Week","Week"&" "&[Value])

Measurez:

Price = 
VAR _P1 =
    CALCULATE (
        MAX ( 'Price'[Price] ),
        FILTER ( 'Price', 'Price'[Week] = MAX ( AllWeek[Week] ) )
    )
VAR _MaxNum =
    MAXX (
        FILTER ( ALL ( 'Price' ), 'Price'[WeekNum] <= MAX ( AllWeek[Value] ) ),
        'Price'[WeekNum]
    )
VAR _P2 =
    CALCULATE (
        MAX ( 'Price'[Price] ),
        FILTER ( 'Price', 'Price'[WeekNum] = _MaxNum )
    )
RETURN
    IF ( _P1 = BLANK (), _P2, _P1 )

Result:

You can download the pbix file from this link: Missing values per week - return latest active value

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.