How to Create an Overdue status if course not taken within 30 days?

[bcbuckley13]

Hi, I've tried several different ways and am struggling.

Scenario: Users take a class that's assigned to them and they have 30 days to complete it, otherwise they are "Overdue" or if they complete it AFTER the Due Date, it is NO.

If they complete it within 30 days, then Yes.

What's causing my issues are the "null" cells for people that haven't completed the course.

How could I write this out?

[v-easonf-msft]

Hi , @bcbuckley13 

Try to create a custom column in query editor:

Something like :

= Table.AddColumn(#"Changed Type", "Custom", each if [Course Complete Date] = null then null else if [Course Complete Date] <= [Due Date] then "Yes" else "No")

 Or  create a conditional column:

sample file attached

Best Regards,
Community Support Team _ Eason

[aj1973]

I think her/his request would be more like this

Regards

[bcbuckley13]

Thanks everyone. Those solutions however don't address if the Completed date column is empty, and the Due date is < than Today's date, then "Overdue".

[v-easonf-msft · Accepted solution]

Hi , @bcbuckley13 

You also can try to create custom columns in query editor.

M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc/RCcAgDATQXfItqKcm7Szi/mvU0opni3/heFySWqV4nB4BQZyoxzFmaa72IMYRGM09xzO/SkkpKVsUSIFUYZUmStPoUpSpKFNRXpSRsu9fm1uwLKK3dHMLfvmtx9/tAg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Reg Date" = _t, #"Due Date" = _t, #"Course Complete Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Reg Date", type date}, {"Due Date", type date}, {"Course Complete Date", type date}}),
    Custom1 = Table.AddColumn(#"Changed Type","New",each if [Course Complete Date]=null then Date.From( DateTime.LocalNow()) else [Course Complete Date]),
    Custom2 = Table.AddColumn(Custom1,"Re",each if [Course Complete Date]=null and [New]<=[Due Date] then null else if [Course Complete Date]=null and [New]>[Due Date] then "No" else if [Course Complete Date]<>null and [New]<=[Due Date] then "Yes" else if [Course Complete Date]<>null and [New]>[Due Date] then "No" else null
)
in
    Custom2

The result will show as below:

pbix attached

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[aj1973]

Hi @bcbuckley13 

Please use this DAX formula in your desktop instead of using M code in Power query.

Completed in 30 Days =

IF (

ISBLANK ( SELECTEDVALUE ( 'Table'[Course Complete Date] ) )

&& SELECTEDVALUE ( 'Table'[Due Date].[Date] ) < TODAY (),

"No",

"Yes"

)

Regards

[aj1973]

Hi @bcbuckley13 

Add a conditionla calculated Column to your Tabel, either in Power BI desktop using DAX or in Power query editor like in the image you are showing.

The added column will be 'Completed in 30 days' and the condition will be applied on the column 'Course completed day'

If the Cell is Null then "No" else "Yes"

Regards