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 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 šŸ˜ž

Need Help
  • AlexisOlson's avatar
    AlexisOlson
    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:

     

4 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    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:

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thank youuuuuuuuu ā¤ļø

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Community Support
    5 years ago

    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.