Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 @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.

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 @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.

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.

Anonymous
Not applicable

Thank you Liang for this.

Anonymous
Not applicable

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"
Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.