Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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"...
Need Help
  • DataNinja777's avatar
    DataNinja777
    2 years ago

    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.  

DataNinja777's avatar
DataNinja777
Icon for Super User rankSuper User
2 years ago

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,  

Anonymous's avatar
Anonymous
Not applicable
2 years ago
But how do I transform that into a formula in PowerBi?
  • DataNinja777's avatar
    DataNinja777
    Icon for Super User rankSuper User
    2 years ago

    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.