Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Add rows for missing months

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...
  • AlexisOlson's avatar
    5 years ago

    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: