Forum Discussion
Difference between MAX date and another date
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.
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.
3 Replies
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.
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.
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.
