DATESBETWEEN is not working with min and max dates

[tavolo89]

I am trying to select a date (or several dates) in my slicer, and then use this to calculate a range of dates from 5 days prior to the earliest date selected to the max date in the slicer. For example, selecting 10th Feb in the slicer should give me a date range from 5th Feb to 10th Feb.

I've created two calculated columns for these:

MinDatePeriodMX = CALCULATE(min('Period A'[Day_Date])-5)

MaxDatePeriodMX = CALCULATE(max('Period A'[Day_Date]))

They seem to work as expected.

However, I can't use them in conjunction with this formula:

# Users - L13W MX = CALCULATE([# Users], DATESBETWEEN('Period A'[Day_Date], [MinDatePeriodMX], [MaxDatePeriodMX]))

Instead of returning the # Users measure for only the dats in red below, it returns either only the date(s) in the slicer or if i disable interactions from the slicer, then it returns all dates there is data for.

I only want the range of dates in the red box above. How do I achieve this?

If I change the formula to "hardcode" the dates then it does work as expected:

# Users - L13W = CALCULATE([# Users], DATESBETWEEN('Period A'[Day_Date], "2023-02-05", "2023-02-10"))

Which makes me think that I'm missing some context somewhere. But I can't for the life of me work out where.

[andhiii079845]

I build the report with your information:

Important: In the table is the table date column and the slicer the date from dimDate column!

Measure = 
CALCULATE([User],DATESBETWEEN(dimDate[Date],[MinDatePeriodMX],[MaxDatePeriodMX]))

[tavolo89]

Thanks for the reply!

'Period A'[Day_Date] is my date dimension table.

[andhiii079845]

You use directly the column 'Period A'[Day_Date] in the slicer and table? This will not work, because you choose a value from 'Period A'[Day_Date] via slicer and this also change the matrix. It is the same column. I think you have to use a separate dimtable for dates. You put this dim table date in the slicer.