Calculating over 30 , 60 and 90 days and output to column

[MarkHughes]

Hello,

Data Source: SharePoint Online list

Developing in: PowerBI Desktop

I have a query about a query that I'm hoping some kind member of the community can help me with,

Issue

I need to have 3 count panels on my report to show a count of items that are overdue for 30, 60 & 90 days. The Column name is "Date Due" and I also have an "Overdue" column. I need assistance creating a calculation to look at the "Date Due" column and if it's 30, 60 or 90 days have passed from todays date, it must input either 30, 60 or 90 into the Overdue column based on the result.

Much appreciated

[Stachu]

in PowerQuery you need to add new column with following syntax:

if Duration.Days(Date.From(DateTime.LocalNow()) - [Date Due])>=90 then 90 else 
if Duration.Days(Date.From(DateTime.LocalNow()) - [Date Due])>=60 then 60 else 
if Duration.Days(Date.From(DateTime.LocalNow()) - [Date Due])>=30 then 30 else 
0

[MarkHughes]

Thank you for the reply.

I've just tried to create a custom column called "Overdue Days" and then apply that but it's giving me an error.

Please see below. Any suggestions?

[Stachu · Accepted solution]

I assumed the [Due Date] is of type Date, while it's actually DateTime
this should work

if Duration.Days(DateTime.LocalNow() - [Date Due])>=90 then 90 else 
if Duration.Days(DateTime.LocalNow() - [Date Due])>=60 then 60 else 
if Duration.Days(DateTime.LocalNow() - [Date Due])>=30 then 30 else 
0

[MarkHughes]

This worked perfectly, thank you

[MarkHughes]

Hello,

Data Source: SharePoint Online list

Developing in: PowerBI Desktop

I have a query about a query that I'm hoping some kind member of the community can help me with,

Issue

I need to have 3 count panels on my report to show a count of items that are overdue for 30, 60 & 90 days. The Column name is "Date Due" and I also have an "Overdue" column. I need assistance creating a calculation to look at the "Date Due" column and if it's 30, 60 or 90 days have passed from todays date, it must input either 30, 60 or 90 into the Overdue column based on the result.

Much appreciated