Forum Discussion

Collazo's avatar
Collazo
New Member
4 years ago
Solved

How to Compare Dates in a Incremental Data Set?

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

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.