Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 😞
Solved! Go to 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:
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.
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:
Thank youuuuuuuuu ❤️
@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?
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |