How to Compare Dates in a Incremental Data Set?

[Collazo]

Hi I'm trying to make a dynamic dashboard where the user could be able to choose 2 dates from 2 different slicers. 

I cannot give a model due to sensitivity but lets try to give a context

Source:

Output date  Project Name   Has Pendency

2022-08-20      Project A               Yes

2022-08-20      Project B                Yes

2022-08-20      Project C                Yes

2022-08-21      Project A                No

2022-08-21      Project B                Yes

2022-08-21      Project C                 Yes

2022-08-21      Project D                No

2022-08-22      Project A                Yes

2022-08-22      Project B                 Yes

2022-08-22      Project C                Yes

2022-08-22      Project D                No

My user should be able to select two date from 2 different slicers and make a comparison like (2022-08-20 my project counting was 3 and it's x less than other selected date).

I tried to do this with What If Parameter but there's no date type to select and I cannot have a blank field to user input a date since the user dont know the dates I have in the backend so needs to be a Dropdown list

Any clues?

Thank you so much in advance

[Anonymous · Accepted solution]

Hi @Collazo ,

I suggest you to create date tables by CALENDAR() or CALENDARAUTO() function.

Date 1 = 
CALENDAR(MIN('Table'[Output date]),MAX('Table'[Output date]))

Date 2 = 
CALENDAR(MIN('Table'[Output date]),MAX('Table'[Output date]))

Create unactive relationship between two date tables and your fact table.

Measure 1 = 
CALCULATE(COUNT('Table'[Project Name]),USERELATIONSHIP('Date 1'[Date],'Table'[Output date]))

Measure 2 = 
CALCULATE(COUNT('Table'[Project Name]),USERELATIONSHIP('Date 2'[Date],'Table'[Output date]))

Diff = 
ABS([Measure 1] - [Measure 2])

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Collazo , Refer if my video can help on this

How to use two Date/Period slicers

https://youtu.be/WSeZr_-MiTg