Forum Discussion

tavolo89's avatar
tavolo89
Frequent Visitor
3 years ago

DATESBETWEEN is not working with min and max dates

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.

datesbetween
DAX

3 Replies

  • andhiii079845's avatar
    andhiii079845
    Icon for Solution Sage rankSolution Sage
    3 years ago

    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.  

  • tavolo89's avatar
    tavolo89
    Frequent Visitor
    3 years ago

    Thanks for the reply!

     

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

  • andhiii079845's avatar
    andhiii079845
    Icon for Solution Sage rankSolution Sage
    3 years ago

    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]))