Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculating days overdue based on selected date

Hi There,

 

I was hoping to get some assistance with my dax formula which calculates days overdue.

 

Days Overdue = if(TODAY()>'Transactions'[Due date],DATEDIFF('Transactions'[Due date],TODAY(),DAY), -1)
 

I want to change this formula to calculate days overdue based on a selected date in filter. Right now it calculates from todays date.

 

 

 

 

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    Hi, Anonymous 

     

    Based on your description, we suggest you use a measure to achieve your requirement. A measure will reflect the selection of the slicer in time, and the column is refreshed only after loading and clicking the Refresh button, and cannot interact with other visual interactions in time.

     

    I created data to reproduce your scenario. The pbix file is attached in the end.

    Transactions:

     

    Calendar(a calculated table):

     

     

     

    Calendar = CALENDARAUTO()

     

     

     

     

    There is no relationship between two tables.

     

    You may create a measure as below.

     

     

     

    Datediff = 
IF(
    ISFILTERED('Calendar'[Date]),
    IF(
        HASONEFILTER('Calendar'[Date]),
        IF(
            SELECTEDVALUE('Calendar'[Date])>SELECTEDVALUE(Transactions[Due Date]),
            DATEDIFF(SELECTEDVALUE(Transactions[Due Date]),SELECTEDVALUE('Calendar'[Date]),DAY),
            -1
        )
    )
)

     

     

     

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

6 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous ,

    You can do it like this using a date slicer based on calendar

    Days Overdue =
    VAR _max = MAXX(Allselected('Calendar'), 'Calendar'[DATE] )
    return
    if(_max>min('Transactions'[Due date]),DATEDIFF(min('Transactions'[Due date]),_max,DAY), -1)

     

    But you need to force a row context to get correct calculation 

     

    Avg Days Overdue =
    AverageX(values(Table[ID]),[Days Overdue])

    Avg Days Overdue =
    AverageX(Summarize(Table,Table[ID],"_1",[Days Overdue]),[_1])

     

    ID can be any group or level where this calculation is correct

     

    Refer, How I used it

    https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Difference-Across/ba-p/934397#M451

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Amit,


      Thanks for that. Is there a possibility of keeping this a calculated column instead of measure as all my viz incorporate this as a column?

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

         I added the measure as a column in my grid visual, but if you wanted a column in your actual data table, you could use:

         

        Overdue = DATEDIFF(TODAY(),'Date'[Date],DAY)
         
        You might want to add an IF statement to ignore dates after today, so you don't get negatives.
         
         

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hello,

    I beleive this measure is what you're looking for!

     

    DaysOverdue = DATEDIFF(TODAY(),SELECTEDVALUE('Date'[Date]),DAY)
     
    Hope that helps!
    ~Kim
  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    6 years ago

    Hi, Anonymous 

     

    Based on your description, we suggest you use a measure to achieve your requirement. A measure will reflect the selection of the slicer in time, and the column is refreshed only after loading and clicking the Refresh button, and cannot interact with other visual interactions in time.

     

    I created data to reproduce your scenario. The pbix file is attached in the end.

    Transactions:

     

    Calendar(a calculated table):

     

     

     

    Calendar = CALENDARAUTO()

     

     

     

     

    There is no relationship between two tables.

     

    You may create a measure as below.

     

     

     

    Datediff = 
IF(
    ISFILTERED('Calendar'[Date]),
    IF(
        HASONEFILTER('Calendar'[Date]),
        IF(
            SELECTEDVALUE('Calendar'[Date])>SELECTEDVALUE(Transactions[Due Date]),
            DATEDIFF(SELECTEDVALUE(Transactions[Due Date]),SELECTEDVALUE('Calendar'[Date]),DAY),
            -1
        )
    )
)

     

     

     

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.