How to capture min & max date and difference from slicer visual?

[Ahmedelshark]

Hello,

I am using the slicer visual to display date range (wrk_date). I would like to create a measure to capture the start and end date selected in the slicer, as well as an additional measure to calculate date difference.

I have tried the following measures but the output is always incorrect. You can see the data column I am working with below. 

Does anyone know what I am doing wrong? Any help would be greatly appreciated!

Date Harvest min = calculate(
MIN('Tool Output'[wrk_date]),
ALLSELECTED(('Tool Output'[wrk_date])
))

Date Harvest max = calculate(
MIN(' Tool Output'[wrk_date]),
ALLSELECTED(('Tool Output'[wrk_date])
))

Date Difference = DATEDIFF(
        [Date Harvest Min],
        [Date Harvest Max],
        DAY
    )

wrk_date

01-Mar-21

03-Mar-21

03-Mar-21

04-Mar-21

05-Mar-21

05-Mar-21

05-Mar-21

08-Mar-21

08-Mar-21

09-Mar-21

12-Mar-21

19-Mar-21

24-Mar-21

25-Mar-21

25-Mar-21

06-Apr-21

07-Apr-21

07-Apr-21

14-Apr-21

16-Apr-21

16-Apr-21

[amitchandak]

@Ahmedelshark , Try a measure like

Days =
var _max = maxx(allselected('Tool Output'), 'Tool Output'[wrk_date])
var _min = minx(allselected('Tool Output'), 'Tool Output'[wrk_date])
return
datediff(_min,_max, day)+1

[Ahmedelshark]

Hi @amitchandak,

Many thanks for your reply. I tried this and it partially works. 

The measure works for dates that are in the dataset. However, when you set the slicer on an end date that is not part of the wrk_date column (such as 14 March 2021), it does not calculate the days correctly anymore. Instead, it defaults to the last available date. 

Is there a measure that can calculate days between the start and end date in the slicer, irrespective of what is in your date column?

[V-lianl-msft · Accepted solution]

Hi @Ahmedelshark ,

Be aware that DAX measure are based on row context. It calculates the value based on your real data.

If you want to calculate the value exactly according to the date in the slicer, please create a calendar table and then create a 1 - * relationship with the date in the fact table.

calendar = CALENDAR(MIN('Table (2)'[wrk_date]),MAX('Table (2)'[wrk_date]))

Use the calendar table as the slicer and apply the earlier measure.

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

[Ahmedelshark]

Hi @V-lianl-msft 

Thank you for your reply. 

I have created the calendar measure using the DAX you provided. What is the next step to create the 1 to many relationship with the fact table? (Sorry I am not familiar with relationships).

[V-lianl-msft]

Create a relationship here and use the newly created table as a date slicer.