topic Re: Datediff using allselected and slicer in DAX Commands and Tips

Datediff using allselected and slicer

Rafaelhk — Wed, 28 Sep 2022 20:49:12 GMT

I have a database that compiles information received from several organization and I have to keep track on how many days has passed since the last submission.

I am doing it using this measure:

Overall last submission date = calculate(MAX(Submissions[last_submission_date]), ALLSELECTED(Submissions))

And then the min of this column

Days from last submissoin= DATEDIFF(Submissions[last_submission_date], Submissions[Overall last submission date], DAY)

When not filtered, the result is as expected:

Organization	Last submission Date	Overall last submission Date	Days from last submission
A	14/8/2022	19/9/2022	36
B	19/9/2022	19/9/2022	1

The problem is when I filter the submission by year, using a slicer:

Expected:

Organization	Last submission Date	Overall last submission Date	Days from last submission
A	31/12/2021	31/12/2021	0
B	31/12/2021	31/12/2021	0

What I get:

Organization	Last submission Date	Overall last submission Date	Days from last submission
A	31/12/2021	31/12/2021	262
B	31/12/2021	31/12/2021	262

Any thoughts on why this is happening?

Re: Datediff using allselected and slicer

amitchandak — Thu, 29 Sep 2022 02:15:56 GMT

Rafaelhk , The second one needs to be a measure too

Days from last submissoin= DATEDIFF(Max(Submissions[last_submission_date]), Submissions[Overall last submission date], DAY)

sumx(values(Submissions[Organization]) ,calculate(DATEDIFF(Max(Submissions[last_submission_date]), Submissions[Overall last submission date], DAY) ) )

Re: Datediff using allselected and slicer

Rafaelhk — Tue, 11 Oct 2022 17:51:56 GMT

amitchandak ,

Thank you for the reply. Although your formula did not work, you pointed me in the right direction.

This is the formular that worked:

Days from last submission = CALCULATE(DATEDIFF(MAX(Submissions[last_submission_date]),Submissions[Overall last submission date], DAY))