I am trying to calculate how many days a payment is over due based on a filtered date

[Bilal_321321]

Hi All

I am having an issue with calculating days overdue based on a filtered date. For example I have the following table:

Client	Value	Due	Days Over
Client A	100	31/01/2020	???
Client B	100	15/02/2020	???
Client X	200	28/02/2020	???
Client Z	300	15/02/2020	???

I want Days Over column to be calculated based on a date filter i select, so for example i select the date 28/02/2020 the following will be filled in:

Client	Value	Due	Days Over
Client A	100	31/01/2020	28
Client B	100	15/02/2020	13
Client X	200	28/02/2020	0
Client Z	300	15/02/2020	-15

I am able to create a report that calculates days over based on todays date using 'Days Over = Today()-[Due]' but i want to be able to change the 'Today()' part to a date i have filtered.

Hope that makes sense.

Thanks in advance

[v-lili6-msft]

hi  @Bilal_321321 

First, you should know that:

1. Calculation column/table not support dynamic changed based on filter or slicer, it is static.
   2. Measure can be affected by filter/slicer, so you can use it to get dynamic summary result.

https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

Second, for your case, you need to create a measure as this way

Step1:

You need a separate date table for the slicer.

Step2:

Then create a measure as below:

Days Over = DATEDIFF(SELECTEDVALUE('Table'[Due]),SELECTEDVALUE('Date for slicer'[Date]),DAY)

Step3:

then drag them into a table visual:

and here is sample pbix file, please try it.

Regards,

Lin

[Bilal_321321]

Thank you, works better than expected.

[Greg_Deckler]

Create a separate Date/Calendar table using CALENDAR or CALENDARAUTO function. Put the Date column from this in a slicer. Then replace TODAY with SELECTEDVALUE('Calendar'[Date])

[parry2k]

@Bilal_321321 same this add as a measure

Days Over = SELETEDVALUE ( DateTable[SelectedDate] ) - SELECTEDVALUE ( Table[Due] )