Forum Discussion
Column to reflect overdue items
Hi,
Need help in creating a new column that will tell me if the item is already overdue, with the following conditions:
- If Planning Date >Date Today and Custom Status = Open, "Overdue"
- If Planning Date < Date Today and Custom Status = Open, "On-Track"
- If Planning Date = Null and Custom Status = Open, "Unplanned"
- If Custom Status = Solved, "Done"
Thank you
Hi Anonymous ,
There are many ways to produce your required output by using Power Query and/ or dax. In order to fix the "Today Date", I used the combined methodology of power query and dax as show below.
First, add a custom column in Power Query to fix today's date and rename the column to "Date Today", as you specified.
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Date.From(DateTime.LocalNow())), #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Custom", "Date Today"}})The in Power BI data model table view, write a calculated column like below:
Overdue status = Switch(True(), 'Table'[PLANNING DATE (UTC)]>'Table'[Date Today]&&'Table'[Custom Status]="Open","Overdue", 'Table'[PLANNING DATE (UTC)]<'Table'[Date Today]&&'Table'[Custom Status]="Open","On-Track", 'Table'[PLANNING DATE (UTC)]=blank()&&'Table'[Custom Status]="Open","Unplanned", 'Table'[Custom Status]="Solved","Done")The resultant output is as shown below:
I attach an example pbix file.
3 Replies
- DataNinja777
Super User
Hi Anonymous ,
You've already written your answer for your case. However, Date Today should be a fixed date when your data was extracted and the correctness of your overdue analysis depends on when the information was extracted as well as the date of the analysis. For example, if the analysis is done 1 week later and Date Today is not fixed like today(), open and not open status might have changed by that time if the customer paid in the meantime. Therefore, normally, overdue analysis is done as of the reference date if the invoice is open on that specific date and compare the status with respect to the due date (planned date). In your case "Date Today", is the date the data was extracted, and normally, accounts receivable in your company's ERP system has this information. Also, solved means that your invoice was paid, and there should be no outstanding balance either for that item.
Best regards,
- DataNinja777
Hi Anonymous ,
There are many ways to produce your required output by using Power Query and/ or dax. In order to fix the "Today Date", I used the combined methodology of power query and dax as show below.
First, add a custom column in Power Query to fix today's date and rename the column to "Date Today", as you specified.
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Date.From(DateTime.LocalNow())), #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Custom", "Date Today"}})The in Power BI data model table view, write a calculated column like below:
Overdue status = Switch(True(), 'Table'[PLANNING DATE (UTC)]>'Table'[Date Today]&&'Table'[Custom Status]="Open","Overdue", 'Table'[PLANNING DATE (UTC)]<'Table'[Date Today]&&'Table'[Custom Status]="Open","On-Track", 'Table'[PLANNING DATE (UTC)]=blank()&&'Table'[Custom Status]="Open","Unplanned", 'Table'[Custom Status]="Solved","Done")The resultant output is as shown below:
I attach an example pbix file.