Add rows for missing months

[Anonymous]

Hi everyone, i hope you can help me.

I have some evaluations for a group of countries, but this evaluations aren't made for every month. 

The data looks like this:

But i need score for every month, if there is a month without evaluation the score should be the last one. Therefore i need yo add the missing months in new rows and take the previous score.

The data should look like this:

Another issue is that the data is in a calculated table so i can't do a merge in edit query 😞

[v-henryk-mstf]

Hi @Anonymous ,

According to your needs, I agree with the method provided by @AlexisOlson . But I think it may be a bit complicated to create by dax formula.

My suggestion is that you can create a data model with a complete month, and then use the fill down function in the power query to meet your needs. The reference is as follows:

Best Regards,
Henry

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

[AlexisOlson · Accepted solution]

Without a date dimension table, this is quite a bit of work since the months are not numbered or ordered.

You can still do it like this though:

Cross =
VAR Cartesian =
    ADDCOLUMNS (
        CROSSJOIN (
            VALUES ( Table1[Country] ),
            SELECTCOLUMNS ( GENERATESERIES ( 1, 12 ), "MonthNo", [Value] )
        ),
        "Month", FORMAT ( DATE ( 2000, [MonthNo], 1 ), "mmmm" )
    )
VAR AddScore =
    ADDCOLUMNS (
        Cartesian,
        "ScoreLookup",
            LOOKUPVALUE (
                Table1[Score],
                Table1[Country], [Country],
                Table1[Month], [Month]
            )
    )
VAR FillScore =
    ADDCOLUMNS (
        AddScore,
        "Score",
            VAR CountryRow = [Country]
            VAR MonthNoRow = [MonthNo]
            VAR LastScoreMonth =
                MAXX (
                    FILTER (
                        AddScore,
                        [Country] = CountryRow
                            && [MonthNo] <= MonthNoRow
                            && NOT ISBLANK ( [ScoreLookup] )
                    ),
                    [MonthNo]
                )
            RETURN
                MAXX (
                    FILTER ( AddScore, [Country] = CountryRow && [MonthNo] = LastScoreMonth ),
                    [ScoreLookup]
                )
    )
RETURN
    SELECTCOLUMNS (
        FillScore,
        "Country", [Country],
        "Month", [Month],
        "Score", [Score]
    )

Here's what this looks like with the helper columns included:

[Anonymous]

Thank youuuuuuuuu ❤️

[AllisonKennedy]

@Anonymous  this can be done easily if you have a DimDate table: https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html

What does your data model look like?