Forum Discussion
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 Number | Date | Attribute | Time deadline (AEST) | Value |
| 1 | 21/04/2022 | Office1 | 31/12/2022 12:00pm | Outstanding |
| 2 | 22/04/2022 | Office2 | 31/12/2022 12:00pm | Outstanding |
| 1 | 21/04/2022 | Office3 | 31/12/2022 12:00pm | Done |
| 3 | 22/04/2022 | Office2 | 31/12/2022 12:00pm | Outstanding |
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
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:
