How to make a classification calculations for dates

[derekli17001]

Lets just say i have data like this and want to make a table on Powerbi show that if an action number is 15 days past the due date, it is classified as Overdue and if it is under 15 days, it is classified as in Progress - how should i do this? Im assuming conditional columns are to be used. Thanks!

[Ritaf1983 · Accepted solution]

Hi @derekli17001 
If I understood you correctly and comparison refers to "today" then add a calculated column with DAX :

status = IF('Table'[Due Date]+15>TODAY(),"in progress" , "Overdue" )

pbix is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[Ritaf1983 · Accepted solution]

Of course, just delete the If statement.

Days over due =

var overdue_date = TODAY()-15

Return

DATEDIFF([Due Date],overdue_date,day)

if you want it without "-" sign , you can use ABS function:

Days over due _ ABS =

var overdue_date = TODAY()-15

Return

ABS (DATEDIFF([Due Date],overdue_date,day))

modified pbix is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[Ritaf1983]

Hi @derekli17001 
Past date from today/slicers selection / other?

[derekli17001]

Past the date shown in the first column.
Eg) The first action number in the picture has a due date of 1/04/2024 (dd/mm/yy) - therefore i want it to be classified as "In progress" as it hasnt be 15 days after the due date. Whereas the Action number at the bottom of the picture has a due date of 28/02/2024 and since its been 15 days past that date - i want it to be classified as "Overdue". Hope this clarifies things - thanks

[Ritaf1983 · Accepted solution]

Hi @derekli17001 
If I understood you correctly and comparison refers to "today" then add a calculated column with DAX :

status = IF('Table'[Due Date]+15>TODAY(),"in progress" , "Overdue" )

pbix is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[derekli17001]

it worked thanks - how do i go about making an additional column that calculates how many days something is overdue by? Thanks

[Ritaf1983]

Hi @derekli17001 
You can use the formula :

Days over due =

var overdue_date = TODAY()-15

Return

IF([status]="overdue",

DATEDIFF([Due Date],overdue_date,day),0)

The updated pbix is attached

[derekli17001]

Thank you -  for items that show 0 days overdue since they havent been overdue yet, is there a way to add into the existing above formula so we can see how many days are left till overdue instead of 0? Thank you for your help

[Ritaf1983 · Accepted solution]

Of course, just delete the If statement.

Days over due =

var overdue_date = TODAY()-15

Return

DATEDIFF([Due Date],overdue_date,day)

if you want it without "-" sign , you can use ABS function:

Days over due _ ABS =

var overdue_date = TODAY()-15

Return

ABS (DATEDIFF([Due Date],overdue_date,day))

modified pbix is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[derekli17001]

Hi i have one more question 

Would it be possible to make a Donut chart showing 3 sections for: 1) how many are 10 days over, 2) 20 days overdue and 3) 30 days + overdue? Thanks!

[Ritaf1983 · Accepted solution]

Hi @derekli17001 
The technical solution :

You can add a column for the classification of the range :

Overdue_range = SWITCH(true(),

'Table'[Days over due]>0&&[Days over due]<=10 ,"1-10",

SWITCH(true(),

'Table'[Days over due]>10&&[Days over due]<=20 ,"11-20",

SWITCH(true(),

'Table'[Days over due]>20 &&[Days over due]<=30 ,"21-30",

SWITCH(true(),

'Table'[Days over due]>30,"31+")

)))

And use this column as a legend of the donut :

In terms of effective data visualization, the donut chart isn't suitable. It's difficult for us to compare the lengths of the donut segments, and it requires interpretation of colors and playing "ping pong" with the legend. This means that interpreting the graph would require unnecessary resources for no reason, and it's a graph that wastes valuable real estate on the dashboard. The appropriate graphs in this scenario are: Since we're examining the quantity of actions exceeding a logical axis (the range of days), and the questions are about ordinality ("where are there many overdue actions and where are there few"), the suitable graph here is a column graph sorted by the logical order of the ranges (like a histogram). For example :

And if the purpose of the donut was to show the distribution of the whole, then it still doesn't change the question that needs to be answered, which is whether the quantity of overdue actions by many days is greater than those overdue by fewer days or how it is distributed. It's essentially the same comparison, just showing different units of measure. And if there's still a need to show some kind of fraction, you can create a graph with a "filling jar" effect using a stacked and framed graph

The absolute qty in this case can be on a tooltip :

The updated pbix with all examples is attached

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