Forum Discussion
Anonymous
5 years agoNot applicable
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...
- 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:
AllisonKennedy
5 years agoCommunity Champion
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?