Datediff using allselected and slicer

[Rafaelhk]

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?

[amitchandak]

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

[Rafaelhk · Accepted solution]

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