Difference between MAX date and another date

[Anonymous]

Hi folks,

I am struggling with a DAX expression to get the difference between the max date in a slicer and the date of the invoices, I mean:

I have a table with clients, invoices and dates:

Client	Invoice	Date
client_1	INV/0001	02/05/2018
client_1	INV/0002	03/07/2018
client_1	INV/0003	08/09/2018
client_2	INV/0005	16/06/2018
client_2	INV/0012	20/11/2018

And I have a slicer to select my dates, so, I would like to get the difference in days between the MAX(date) in the slicer and each one of my dates, like the following example:

Client	Invoice	Date	MAX(date) in selector	Days
client_1	INV/0001	02/05/2018	15/12/2018	227
client_1	INV/0002	03/07/2018	15/12/2018	165
client_1	INV/0003	08/09/2018	15/12/2018	98
client_2	INV/0005	16/06/2018	15/12/2018	182
client_2	INV/0012	20/11/2018	15/12/2018	25

Where MAX(date) in selector comes from the slicer.

It would be nice if someone could help me, thanks in advance.

[v-eachen-msft · Accepted solution]

Hi @Anonymous ,

You could create two measures to get it.

max date =
MAXX ( ALLSELECTED ( 'Date'[Date] ), 'Date'[Date] )

Days =
DATEDIFF ( SELECTEDVALUE ( 'Table'[Date] ), [max date], DAY )

Here is the result.

Here is my test file for your reference.

[Anonymous]

Hi @v-eachen-msft 

could it be possible to do it in a column instead of doing it in a measure?  The reason is that I want to use this calculated value in a selector.

[Mariusz]

Hi @Anonymous 

You can create a measure like below.

Days = DATEDIFF( SELECTEDVALUE( 'Table'[Date] ), MAX( dates[Date] ), DAY )

Please see the attached file with the solution.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.