Filling blanks with previous available week data

[Demidovich]

Hello everyone.

I need some help here.

My raw data is shown below

In case of any blank in "Deviation" column, I want to replace it with the data in previous available week for the project.

In order to do that I created a calculated column "Filled_deviation":

Filled_Deviation = 
VAR CAL =
    CALCULATE (
        FIRSTNONBLANK ( 'Table'[Deviation], 'Table'[Deviation] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Title] ),
            EARLIER ( 'Table'[WeekNum] ) > 'Table'[WeekNum]
        )
    )
RETURN
    IF ( ISBLANK ( 'Table'[Deviation] ), CAL, 'Table'[Deviation] )

and got this result:

As you can see, this formula works almost fine except for a couple of projects:

project 2 should have "0" for the 16th week,

project 14 should have "10" for the 16th week,

and project 18 should have "11" for that week.

This is my .pbix file if you need it: https://drive.google.com/open?id=1k_DpRupUxGiMHr70FfZl2OT4FetjRBSp

Tell me please what I'm doing wrong.

Any help would be very appreciated.

[MFelix]

Hi @Demidovich,

On the query editor choose the deviation column and fill down should work as you need.

Regards,

MFelix

[Demidovich]

Hi @MFelix ,
Thank you for your response and trying to help.
Please note that I need to fill column with the latest available data.
For example, “project 6” has no value on 13-15 weeks, so right values on 13-16 weeks would be “blank, blank, blank , 0”.
“Project 15” has no value on 13th week, in this particular case values should be “blank, 0, 0, 0”.