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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
ringovski
Helper II
Helper II

KPI based on Datetime

Hi All,

I have a series of tasks 1 to 60 (rows) with different dead lines (datetime) and different locations (attribute) record 'done' when they have completed the task.  

 

The requirement is if 'done' is not entered by the deadline then the KPI should be red, once 'done' is entered either before the deadline or after it turns green and I can only use the standard visuals provided in PBI. So I'm not sure if this is possible with the standard KPI visual.

 

Table name: Data    
Task NumberDateAttributeTime deadline (AEST)Value
121/04/2022Office131/12/2022 12:00pmOutstanding
222/04/2022Office231/12/2022 12:00pmOutstanding
121/04/2022Office331/12/2022 12:00pmDone
322/04/2022Office231/12/2022 12:00pmOutstanding

 

tried adding column to table but can't mix data types text & date;

KPI Column = IF('Data'[Deadline] > time(hour(now()),minute(now()),second(now()) && 'Data'[Value] = "Outstanding", 1,0)

 

I'm out of ideas so thanks for any assistance.

1 REPLY 1
SpartaBI
Community Champion
Community Champion

The date and deadline columns are not the same type? One of them is Text for some reason? If yes, change it to data or datetime before you bring it to the model (in Power Query for example).
Than you sould be able to write something simple like:
KPI Column = IF('Data'[Deadline] > 'Data'[Deadline] && 'Data'[Value] = "Outstanding", 1,0)
Also you don't need a column for that calculation, do it in a measure and than use it in the conditional formating settings of the visual:

SpartaBI_1-1650443383205.png

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.