Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Power BI File: https://1drv.ms/u/s!Aq5na0w9TsXOhppPk_9SVfB_Wx6zwA?e=ded6fF
Hi All,
Really hope someone can help me with the questions below, I tried a few things but not having a lot of luck.
FiscalYear | Year | Period | KPI | Value | FactTarget.Target_String | FactTarget.Target_Num | FactTarget.Target_Percent |
2021 | 2020-2021 | Apr | 4.8 | 0.17 | 0.4 | 0.4 | 0.4 |
2021 | 2020-2021 | May | 4.8 | 0.18 | 0.4 | 0.4 | 0.4 |
2021 | 2020-2021 | Jun | 4.8 | 0.19 | 0.4 | 0.4 | 0.4 |
2021 | 2020-2021 | Apr | 4.6 | 151 | 800 | 800 | 800 |
2021 | 2020-2021 | May | 4.6 | 173 | 800 | 800 | 800 |
2021 | 2020-2021 | Jun | 4.6 | 259 | 800 | 800 | 800 |
2021 | 2020-2021 | Jul | 4.8 | 0.2 | 0.4 | 0.4 | 0.4 |
2021 | 2020-2021 | Jul | 4.6 | 488 | 800 | 800 | 800 |
2021 | 2020-2021 | Aug | 4.8 | 0.2 | 0.4 | 0.4 | 0.4 |
2021 | 2020-2021 | Aug | 4.6 | 491 | 800 | 800 | 800 |
2021 | 2020-2021 | Sep | 4.8 | 0.22 | 0.4 | 0.4 | 0.4 |
2021 | 2020-2021 | Sep | 4.6 | 570 | 800 | 800 | 800 |
2021 | 2020-2021 | Oct | 4.8 | 0.4 | 0.4 | 0.4 | |
2021 | 2020-2021 | Oct | 4.6 | 622 | 800 | 800 | 800 |
2021 | 2020-2021 | Nov | 4.8 | 0.4 | 0.4 | 0.4 | |
2021 | 2020-2021 | Nov | 4.6 | 784 | 800 | 800 | 800 |
2021 | 2020-2021 | Q1 | 5.3 | 1255 | 1000 | 1000 | 1000 |
2021 | 2020-2021 | Q2 | 5.3 | 1246 | 1000 | 1000 | 1000 |
2021 | 2020-2021 | Q3 | 5.3 | 1240 | 1000 | 1000 | 1000 |
2021 | 2020-2021 | Q1 | 4.3 | 8 | 8 | 8 | |
2021 | 2020-2021 | Q2 | 4.3 | 8 | 8 | 8 | |
2021 | 2020-2021 | Q3 | 4.3 | 8 | 8 | 8 | |
2021 | 2020-2021 | Q4 | 5.3 | 1230 | 1000 | 1000 | 1000 |
2021 | 2020-2021 | Q4 | 4.3 | 8 | 8 | 8 | |
2022 | 2021-2022 | Q1 | 5.3 | 1231 | 1000 | 1000 | 1000 |
2022 | 2021-2022 | Q1 | 4.3 | 8 | 8 | 8 | 8 |
Solved! Go to Solution.
Hi @Anonymous ,
To get the value of the most recent KPI, you need to take the date as the condition.If you do not have a date formatted column,Please create new date column.
If you need to create a card visual,please create different measures for different KPIs to display.
Another way is to use conditional formatting in a matrix or table.
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Please check the M query in the pbix file, which is the method of converting to date.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZJNC4JAEIb/SnhW2U93PHoNKqKjeIiILlISGfTvc1ZXdsN014PvLLIPz8xoWUaMMBrFWEgynIvm2aVIoUuSUqWLcLKKJ8nd+WOT4E9u27tN5v6k6Tbrkkp8A4Q4udCsBhX3B02vCDKZh4C1NSQL2U49GgWAv7Fob+uMBtTGPGCrp2tjGwOUhkSlVMRfebi8RuXG32cw9GW6UU/f/vFe4zMY+hQIf98RQ6b4h1ImJRaiGbv8Y5nFiiyM5Tb7Cy2wVM/KhxXB+Mz1GXKfB94X1iw8cBYx72L9fZoMZ+d7cTrrmmR7F7iu6gs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FiscalYear = _t, Year = _t, Period = _t, KPI = _t, Value = _t, FactTarget.Target_String = _t, FactTarget.Target_Num = _t, FactTarget.Target_Percent = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"FiscalYear", Int64.Type}, {"Year", type text}, {"Period", type text}, {"KPI", type number}, {"Value", type number}, {"FactTarget.Target_String", type number}, {"FactTarget.Target_Num", type number}, {"FactTarget.Target_Percent", type number}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Month", each if [Period] = "Q1" then "Mar" else if [Period] = "Q2" then "Jun" else if [Period] = "Q3" then "Sep" else if [Period] = "Q4" then "Dec" else [Period]),
#"Inserted Merged Column" = Table.AddColumn(#"Added Conditional Column", "date", each Text.Combine({Text.From([FiscalYear], "en-US"), [Month]}, " "), type text),
#"Changed Type1" = Table.TransformColumnTypes(#"Inserted Merged Column",{{"date", type date}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Month"})
in
#"Removed Columns"
Hi @Anonymous ,
To get the value of the most recent KPI, you need to take the date as the condition.If you do not have a date formatted column,Please create new date column.
If you need to create a card visual,please create different measures for different KPIs to display.
Another way is to use conditional formatting in a matrix or table.
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you Liang for this.
Thank you Liang,
Unfortunately I have not been able to use dates because the records sometimes comes in quarters, without the month or day of the month.
Thanks
Victor
Please check the M query in the pbix file, which is the method of converting to date.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZJNC4JAEIb/SnhW2U93PHoNKqKjeIiILlISGfTvc1ZXdsN014PvLLIPz8xoWUaMMBrFWEgynIvm2aVIoUuSUqWLcLKKJ8nd+WOT4E9u27tN5v6k6Tbrkkp8A4Q4udCsBhX3B02vCDKZh4C1NSQL2U49GgWAv7Fob+uMBtTGPGCrp2tjGwOUhkSlVMRfebi8RuXG32cw9GW6UU/f/vFe4zMY+hQIf98RQ6b4h1ImJRaiGbv8Y5nFiiyM5Tb7Cy2wVM/KhxXB+Mz1GXKfB94X1iw8cBYx72L9fZoMZ+d7cTrrmmR7F7iu6gs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FiscalYear = _t, Year = _t, Period = _t, KPI = _t, Value = _t, FactTarget.Target_String = _t, FactTarget.Target_Num = _t, FactTarget.Target_Percent = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"FiscalYear", Int64.Type}, {"Year", type text}, {"Period", type text}, {"KPI", type number}, {"Value", type number}, {"FactTarget.Target_String", type number}, {"FactTarget.Target_Num", type number}, {"FactTarget.Target_Percent", type number}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Month", each if [Period] = "Q1" then "Mar" else if [Period] = "Q2" then "Jun" else if [Period] = "Q3" then "Sep" else if [Period] = "Q4" then "Dec" else [Period]),
#"Inserted Merged Column" = Table.AddColumn(#"Added Conditional Column", "date", each Text.Combine({Text.From([FiscalYear], "en-US"), [Month]}, " "), type text),
#"Changed Type1" = Table.TransformColumnTypes(#"Inserted Merged Column",{{"date", type date}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Month"})
in
#"Removed Columns"
This is amazing, works like a dream, than you very much for pointing out the script 😁
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |