Forum Discussion

medwards's avatar
medwards
Frequent Visitor
3 years ago

Calculate Past Due Users + Past Due Days

Sample data below.

 

Hope you can kindly assist! I'm trying to calculate # users where the status is not "Completed" after a due date (let's say the due date is 3/1/2023) grouped by status. I'm very new to Power BI and haven't touched dates yet. Please note the due date is not in the data sheet, do I need to create a measure with the due date or just enter it in a formula?

 

In the end, it would look something like this:
Registered | 5
In Progress | 7

 

Also, in a separate chart, I'm trying to display # users where the status is not "Completed" after a due date but this time grouped by # of days past due 1-5 days, 6-10, 10-15, 15-30, and 30+. Preferably in a bar chart.

 

This is a real head banger, so appreciate any help!

 

User IDStatusAssigned DateCompleted Date
12345Completed1/30/20232/3/2023
87634Completed1/30/20232/5/2023
87635Completed1/30/20232/28/2023
87636Completed1/30/20233/10/2023
87637Completed1/30/20233/7/2023
87638Completed1/30/20233/30/2023
76349Registered1/30/2023 
45787Registered1/30/2023 
87873Registered1/30/2023 
87837Registered1/30/2023 
98769Registered1/30/2023 
98770In Progress1/30/2023 
98771In Progress1/30/2023 
98772In Progress1/30/2023 
98773In Progress1/30/2023 
98774In Progress1/30/2023 
98775In Progress1/30/2023 
98776In Progress1/30/2023 
Need Help

3 Replies

  • DanielGarcia's avatar
    DanielGarcia
    Icon for Advocate I rankAdvocate I
    3 years ago

    Good Nigth man, 

     

    Have any ways to do this things, let's divide in two questions:

     

    1 - In the first answer, yes, you need to put this date in this table or make a relation to a table that have this value.

    2 - In the second question do you need make a neu measure related the data, for exemple:

     

    Days =
        SWITCH(TRUE(),
            Datediff<1,BLANK(),
            Datediff<=3,"1 a 3",
            Datediff<=5,"3 a 5",
            Datediff<=8,"5 a 8",
            Datediff<=12,"8 a 12",
            Datediff<=15,"12 a 15",
            Datediff<=18,"15 a 18","19+")
     
    If you want can send the .pbi
  • medwards's avatar
    medwards
    Frequent Visitor
    3 years ago

    i'm still not clear how to craft a formula for the first one, especially using date.

     

    for the second, how does it know to take the datediff only where the status is not "Completed" after a due date. i think im missing something.

     

    sure if you have a pbi or screenshot based on the sample data i shared, i can take a look

  • medwards's avatar
    medwards
    Frequent Visitor
    3 years ago

    I think i figured out the formula for the first one. This took a lot of handbanging but wanted to share the solution to save someone else the headache.

     

    1a. I created a new column to populate Due Date (let's say 30 days passed assigned date). Example:

    Due Date = 'TableName'[Assigned Date]+30) and just made sure the column data type was set to Date format.
     
    1b. Then, I created a new measure to count past due users. 
    Past Due Users = CALCULATE(
    COUNTROWS('TableName'),
    FILTER(
    'TableName',
    'TableName'[Status] <> "Completed"
    && 'TableName'[Due Date] < TODAY()
    )
    )
     
    1c. To group by status, I just dragged Status and Past Due Users to the Values section.
     
    Ok just need to figure out the second chart!