Forum Discussion

dhandapani's avatar
dhandapani
Frequent Visitor
4 years ago
Solved

Query to Conditioned aging calculation

Hello All, I'm very much new to Power BI. I have a complicated item to deal with.

 

I have a data with dates in mutiple cloumns. I need to calculate the aging based upon following conditions.

 

1.  Calulate ageing with date in PO date

2. If PO date has no value, go to Delivery Date and calculate aging from that value

3. If PO date and Delivery date is blank, go to EX date and calculate aging.

Spoiler

 In summary, i need to look every column one by one and calculate ageing with Or condition. 

Need Help
  • BA_Pete's avatar
    BA_Pete
    4 years ago

    Hi dhandapani ,

     

    When you say 'aging' I'm assuming you mean days between the target date and today. If so, then try this as a new custom column:

    let
  Date.Today = Date.From(DateTime.LocalNow())
in
if [PO Date] <> null then Duration.Days(Date.Today - [PO Date])
else if [Delivery Date] <> null then Duration.Days(Date.Today - [Delivery Date])
else if [Ex Date] <> null then Duration.Days(Date.Today - [Ex Date])
else // your escape value if no condition is true e.g. null, or "Error" etc.

     

    Pete

1 Reply

  • BA_Pete's avatar
    BA_Pete
    Super User
    4 years ago

    Hi dhandapani ,

     

    When you say 'aging' I'm assuming you mean days between the target date and today. If so, then try this as a new custom column:

    let
  Date.Today = Date.From(DateTime.LocalNow())
in
if [PO Date] <> null then Duration.Days(Date.Today - [PO Date])
else if [Delivery Date] <> null then Duration.Days(Date.Today - [Delivery Date])
else if [Ex Date] <> null then Duration.Days(Date.Today - [Ex Date])
else // your escape value if no condition is true e.g. null, or "Error" etc.

     

    Pete