Skip to main content
cancel
Showing results for 
Search instead 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

Reply
derekli17001
Frequent Visitor

How to make a classification calculations for dates

derekli17001_0-1712715015702.png

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

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" )
Ritaf1983_0-1712716613885.png

pbix is attached

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

 

View solution in original post

Of course, just delete the If statement.

Days over due =
var overdue_date = TODAY()-15
Return

DATEDIFF([Due Date],overdue_date,day)
Ritaf1983_0-1712811048542.png

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))
Ritaf1983_1-1712811164962.png

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

View solution in original post

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+")

)))
Ritaf1983_0-1714368363038.png

And use this column as a legend of the donut :

Ritaf1983_1-1714368454900.png

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 :

Ritaf1983_2-1714369086054.png

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

Ritaf1983_3-1714369483213.png

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

Ritaf1983_4-1714369535876.png

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.

View solution in original post

9 REPLIES 9
Ritaf1983
Super User
Super User

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

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

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" )
Ritaf1983_0-1712716613885.png

pbix is attached

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

 

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

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)
Ritaf1983_0-1712803041553.png

The updated pbix is attached

 

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

Of course, just delete the If statement.

Days over due =
var overdue_date = TODAY()-15
Return

DATEDIFF([Due Date],overdue_date,day)
Ritaf1983_0-1712811048542.png

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))
Ritaf1983_1-1712811164962.png

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

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!

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+")

)))
Ritaf1983_0-1714368363038.png

And use this column as a legend of the donut :

Ritaf1983_1-1714368454900.png

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 :

Ritaf1983_2-1714369086054.png

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

Ritaf1983_3-1714369483213.png

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

Ritaf1983_4-1714369535876.png

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.

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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