Calculate the number of days between 2 dates based of a secondary column (flag)

[Anonymous]

Hi all, 

I am looking to show the number of days since latest date selected (from slicer) and annual leave recorded. A slicer also on dahsboard of the employee ID

I have column with everyday in it (calendardate) and a column that flags if annual leave is taken (1) or if it is not taken (0). 

Example data set below:

Calendar Date	ID	Annual Leave
02/02/2023	1001	0
03/02/2023	1001	1
04/02/2023	1001	0
05/02/2023	1001	0
06/02/2023	1001	0
07/02/2023	1001	0
08/02/2023	1001	0
09/02/2023	1001	0

So for example data set above - date slicer set at 09/02/2022 - i would expect my KPI card to show 6 days since annual leave?

Any help would be great. Thanks

[lbendlin]

That looks straightforward.  What have you tried and where are you stuck?

[Anonymous]

Hi, 

To be honest I have tried a number of things and nothing seems to be working... My theory behind it was to calculate max date where =1 in Annual leave column and then subtract that from today but that doesn't seem to be working... your help would be greatly appreciated.

Thanks 

[lbendlin · Accepted solution]

see attached.

[Anonymous]

Thanks very much... worked perfectly.