cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Alumanah
Frequent Visitor

Showing most recent value in cards

 

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.

  1. Is it possible to show the most recent kpi value
  2. Is it possible to have the most recent kpi value green if it meets 100% of target, Orange if 80% above and Red if below 80%
  3. Is it possible to have a piechart on the end that shows green if it meets 100% of target, Orange if 80% above and Red if below 80%                 https://1drv.ms/u/s!Aq5na0w9TsXOhppPk_9SVfB_Wx6zwA?e=ded6fF 

 

 

 

 

Alumanah_0-1635463055659.png

 

FiscalYearYearPeriodKPIValueFactTarget.Target_StringFactTarget.Target_NumFactTarget.Target_Percent
20212020-2021Apr4.80.170.40.40.4
20212020-2021May4.80.180.40.40.4
20212020-2021Jun4.80.190.40.40.4
20212020-2021Apr4.6151800800800
20212020-2021May4.6173800800800
20212020-2021Jun4.6259800800800
20212020-2021Jul4.80.20.40.40.4
20212020-2021Jul4.6488800800800
20212020-2021Aug4.80.20.40.40.4
20212020-2021Aug4.6491800800800
20212020-2021Sep4.80.220.40.40.4
20212020-2021Sep4.6570800800800
20212020-2021Oct4.8 0.40.40.4
20212020-2021Oct4.6622800800800
20212020-2021Nov4.8 0.40.40.4
20212020-2021Nov4.6784800800800
20212020-2021Q15.31255100010001000
20212020-2021Q25.31246100010001000
20212020-2021Q35.31240100010001000
20212020-2021Q14.3 888
20212020-2021Q24.3 888
20212020-2021Q34.3 888
20212020-2021Q45.31230100010001000
20212020-2021Q44.3 888
20222021-2022Q15.31231100010001000
20222021-2022Q14.38888
2 ACCEPTED SOLUTIONS
V-lianl-msft
Community Support
Community Support

Hi @Alumanah ,

 

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.

Vlianlmsft_0-1635749297320.png

 

 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

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"

View solution in original post

5 REPLIES 5
V-lianl-msft
Community Support
Community Support

Hi @Alumanah ,

 

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.

Vlianlmsft_0-1635749297320.png

 

 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

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"

View solution in original post

This is amazing, works like a dream, than you very much for pointing out the script 😁

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors