Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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?
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
This worked perfectly, thank you
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |