cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## How to make a classification calculations for dates

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!

3 ACCEPTED SOLUTIONS
Super User

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

Super User

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

Super User

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.

9 REPLIES 9
Super User

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

Frequent Visitor

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

Super User

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

Frequent Visitor

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

Super User

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

Frequent Visitor

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

Super User

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

Frequent Visitor

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!

Super User

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors