Calculate Past Due Users + Past Due Days

[medwards]

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 ID	Status	Assigned Date	Completed Date
12345	Completed	1/30/2023	2/3/2023
87634	Completed	1/30/2023	2/5/2023
87635	Completed	1/30/2023	2/28/2023
87636	Completed	1/30/2023	3/10/2023
87637	Completed	1/30/2023	3/7/2023
87638	Completed	1/30/2023	3/30/2023
76349	Registered	1/30/2023
45787	Registered	1/30/2023
87873	Registered	1/30/2023
87837	Registered	1/30/2023
98769	Registered	1/30/2023
98770	In Progress	1/30/2023
98771	In Progress	1/30/2023
98772	In Progress	1/30/2023
98773	In Progress	1/30/2023
98774	In Progress	1/30/2023
98775	In Progress	1/30/2023
98776	In Progress	1/30/2023

[medwards]

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! 

[medwards]

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

[DanielGarcia]

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