Fill Empty spaces with previous (column) value (Matrix Visual)

[Anonymous]

Hello.
I Have a problem with filling empty cells that i think is a little bit different from the ones posted in the community, i guess.

So I have this dataset that has a row for everytime the status of the project is updated, with the date that is being updated and the new and previous value of the status.
I need to display a table with the evolution of the status over the time but some weeks the project status isn't updated and i dont have values for that week, so i would like to fill the empty cell with the value from the last updated week(column).

Here I have e example of my dataset:
Dataset Example

Result i'm getting:

In my full dataset i have all the 52 week column so there are a lot of empty spaces to fulfill with value from last updated week.
Is there anyway to do this? I tried multiple ways but this is the simplest to explain.

Thanks in advance

[PaulDBrown]

@Anonymous 
See if this works for you. First the model:

Then the measures:

1) A measure to return the last "new value" by date in the filter context:

New Value (measure) =
VAR MaxDate =
    MAX ( 'Calendar Table'[Date] )
RETURN
    CALCULATE (
        MAX ( 'Table'[New Value] ),
        FILTER ( 'Calendar Table', 'Calendar Table'[Date] = MaxDate )
    )

2) the final measure returning the max new value to fill the blank weeks for each id:

Fill blank rows =
VAR PrevDate =
    MAXX (
        FILTER (
            ALL ( 'Calendar Table' ),
            'Calendar Table'[Date] < MAX ( 'Calendar Table'[Date] )
                && NOT ( ISBLANK ( [New Value (measure)] ) )
        ),
        'Calendar Table'[Date]
    )
VAR SalesFill =
    CALCULATE (
        MAX ( 'Table'[New Value] ),
        FILTER ( ALL ( 'Calendar Table'[Date] ), 'Calendar Table'[Date] = PrevDate )
    )
RETURN
    COALESCE ( [New Value (measure)], SalesFill )

and you get this:

I've attached the sample PBIX file

[Anonymous]

Thank you Paul.
I think it will work, but currently im having trouble with it. Somehow with my data it doesn't work because it is creating a "null" week and then only shows the value of that week for the rest of the spaces. The original Table looks ok. See below:

I think there's something to do with Date type/Region, because my date type is Date/Time, but even when i convert it to Date it wont work. I tried several ways and cant get it to work.
I attached your Pbix File  with some of "my data".

Thank you.

[PaulDBrown]

Hi there. Thanks for the file, but can you release the download? Currently it's asking for a login.

[Anonymous]

Ofc, sorry . I think its okay now.

[PaulDBrown · Accepted solution]

@Anonymous 

Ok, I've had to change some column data types (you had dates as text for example) and changed the relationship from the Calendar table to the fact table: the fields in a relationship must be the same data type, so a date must be linked to a date (not Date/Time). 
Since some ID are updated on the same day, I've also had to change the measures to take this into account (so we are returning the value based on the max date/time and not just date).
Also beware that matrix visualisations are limited in the number of columns which can de shown (So if you include several years and weeks, the visual will be truncated if you have years and weeks as columns. Consider instead using a visual with the IDs as columns and the years and weeks as rows).
Since the sample data covers serveral years, we also need to include either a slicer for the year or Year as an actual field in visuals. Otherwise, if we leave out the year, the measures won't necessarily work since they are coded to lookup by dates; if we just leave the visual with weeks, you can get a wrong values. (Week fields on their own are not "linear" unless they are confined to a single year. So if an Id has values from September 2020 to July of 2021, and you don't filter by year, you will see values for 2021 from January to July; and values for 2020 from September to December which is probably not what you are expecting in this case).

I hope this helps!

I've attached the updated file

[Anonymous]

Thank you Paul. It works like a charm now.

Yeah i was trying something with dates as a text and forget to convert it to date before send you.
About the year slicer yes, i will mostly show way less values in the final visualization with another filter other than the Year. Anyway thank you for the tip and for the help.
Thank you once again Paul.

PS. Also if anyone wants the same as me but with "Traffic Lights" instead of String text I'm attaching a PBIX file with a way to do it (there's probably a simplier way but this works perfectly and saves you the trouble).

[Anonymous]

Thanks for the quick reply.
I Tried something similar already and tried that again but it wont work in matrix. Prob doing something wrong. Can you send me the pbix file please?

Also getting the error that sum cannot work with string, but when i change "Green, Yellow, Red" to "1,2,3" if i have two updates in the same week it will sum the two values.

Thank you

[amitchandak]

@Anonymous , with help from date table and week rank you can get the status of this week and last week

new column in date table 

Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

measures
This Week Status = CALCULATE(sum('Table'[Status ]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week Status = CALCULATE(sum('Table'[Status ]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

New measure

if(isblank([This Week Status], [Last Week Status], [This Week Status])

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.