Calculating days overdue based on selected date

[Anonymous]

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.

[v-alq-msft · Accepted solution]

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.

[Anonymous]

Hello,

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

DaysOverdue = DATEDIFF(TODAY(),SELECTEDVALUE('Date'[Date]),DAY)

Hope that helps!

~Kim

[amitchandak]

@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]

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]

 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]

@Anonymous  Thanks but I want to replace the today with a date selected by the user in slicer/filter. How can I change it?. Thanks