In a summarize function, the date range is being ignored

[MRUry7]

Hi, I have a summarized table and I'm using a date range in the filter to capture records.
When I change the date to something else, the range stays the same.
I think it's ignoring the dates, and giving me the entire table, not sure the fix.

Here is my code

---------------------------------------------------
Summary1 =

--everything

VAR _SelStart ='_CalendarSlicer'[MINDate]

VAR _SelEnd ='_CalendarSlicer'[MAXDate]

VAR _SummaryTable1 = SUMMARIZE (

    FILTER (

        'Referrer Excel',

        'Referrer Excel'[Date] >= _SelStart && 'Referrer Excel'[Date] <= _SelEnd

    ),

     'Referrer Excel'[Referrer Type],

    "Total Count", Count('Referrer Excel'[Referrer Type No])

)

RETURN _SummaryTable1
-----------------------------------------------
nothing I do with the _SelStart and _SelEnd change the results.
I've manually replaced them with actual dates, and then where i was getting the entire table, now getting nothing.
I've confirmed I'm passing legitimate dates and the range matches what's in the table but getting nothing.

I'm thinking that by default it's returning everything in the table grouped, which means maybe I'm doing it wrong?

Any help is appreciated..
Thanks, Steve

[v-jiewu-msft]

Hi @MRUry7 ,

Please try the following methods and check if they can solve your problem:

1.Create the simple table.

2.Create the new table to calculate.

SummaryTable 1 = 
VAR _SelStart = MIN(DateTable[Date])
VAR _SelEnd = MAX(DateTable[Date])
//VAR dif_start = DATEVALUE("1/10/2024 12:00:00 AM")
//VAR dif_end = DATEVALUE("2/10/2024 12:00:00 AM")

VAR _SummaryTable1 = SUMMARIZE (
    FILTER (
        'Table',
        'Table'[Date] >= _SelStart && 'Table'[Date] <= _SelEnd
        //'Table'[Date] >= dif_start && 'Table'[Date] <= dif_end
    ),
    "Total Count", sum('Table'[Quantity])
)
RETURN _SummaryTable1

3.Drag the table into the table visual.

4.Change the date range to capture. The result is shown below.

SummaryTable 1 = 
//VAR _SelStart = MIN(DateTable[Date])
//VAR _SelEnd = MAX(DateTable[Date])
VAR dif_start = DATEVALUE("1/10/2024 12:00:00 AM")
VAR dif_end = DATEVALUE("2/10/2024 12:00:00 AM")

VAR _SummaryTable1 = SUMMARIZE (
    FILTER (
        'Table',
        //'Table'[Date] >= _SelStart && 'Table'[Date] <= _SelEnd
        'Table'[Date] >= dif_start && 'Table'[Date] <= dif_end
    ),
    "Total Count", sum('Table'[Quantity])
)
RETURN _SummaryTable1

Best Regards,

Wisdom Wu

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

[MRUry7]

Thanks Widsom. I'll try that.
Would you have any ideas of why my code would ignore the date range?
I thought it was a format issue so i changed it to string instead of dates, and that produced an error.
But I've used the summarize function before and this is the first time it's happened.
Frustrating.

[HotChilli]

Post a small data sample(that shows the problem) and the adjusted code and I'll have a look at it.

[MRUry7]

Hi and thanks.  Here's a sample of the data.
What the SUMMARIZE code at the bottom of this message is doing, is that it's igoring dates assigned to it in the variables _Selstart and _SelEnd. I checked each variable and the date has been assigned.

The weird is, when i hardcode the date into the SUMMARIZE ex. MIN('Satisfaction'[MetricDate]), instead of the variable I've assigned it to ,_SelStart, it works.

This is consistent for date I try, whether it's from a table or a measure. I don't understand why that happens, my only guess is for what I'm trying to do , I need to revised the summarize section of my code?

Date	Referrer Type
1/1/2023	Internal
1/2/2023	Other
1/3/2023	Internal
1/4/2023	Search
1/5/2023	Internal
1/6/2023	Other
1/7/2023	Search
1/8/2023	Direct
1/9/2023	Social
1/10/2023	Direct
1/11/2023	Social
1/12/2023	Other
1/13/2023	Internal
1/14/2023	Social

So I have 6 date periods that I let the user choose off the list from a drop down. You'll see in the code that i have a switch that says, if the user chooses one period, assign a start and end date to each value, then pass it into the summarize table design.

The code below

Referrer excel Summary1 =

---------------------------------------------------------------------------

-------------------------------------------------------------------------

// This is the recent one, the Summary 2 is the next one.

//Slicer Selection

VAR _SEL = SELECTEDVALUE('_CalendarSlicer'[Period])

//--------------------------------------------------------

VAR _SelStart = SWITCH(_SEL,

    "Everything",MIN('Satisfaction'[MetricDate]),

    "Last Month",MIN('_CalendarFilterDates'[Last Month]),

    "Last Year", MIN('_CalendarFilterDates'[Last Year]),

    "This Month",MIN('_CalendarFilterDates'[ThisMonth]),

    "This Year", MIN('_CalendarFilterDates'[This Year]),

    "Year before last",MIN('_CalendarFilterDates'[Two Years Ago])

    )

VAR _SelEnd = SWITCH(_SEL,

    "Everything",MAX('Satisfaction'[MetricDate]),

    "Last Month",MAX('_CalendarFilterDates'[Last Month]),

    "Last Year", MAX('_CalendarFilterDates'[Last Year]),

    "This Month",MAX('_CalendarFilterDates'[ThisMonth]),

    "This Year", MAX('_CalendarFilterDates'[This Year]),

    "Year before last",MAX('_CalendarFilterDates'[Two Years Ago])

    )

--------------------------------------------------------------------------

VAR _SummaryTable1 = SUMMARIZE (

    FILTER (

        'Referrer Excel',

      'Referrer Excel'[Date] >=_SelStart && 'Referrer Excel'[Date] <= _SelEnd

  ),

    'Referrer Excel'[Referrer Type],

    "Total Count", Count('Referrer Excel'[Referrer Type No])

)

RETURN _SummaryTable1

[HotChilli]

Summary1 is a new table created in Data View?  In that case it won't be dynamic (it won't react to slicers)

[MRUry7]

Thanks but I'm not even testing the slicers.  I'm literally just manually changing the date ranges and the Summarized table is completely ignoring the different dates (and I am checking to make sure that the date range is legit).

So the question, why does the summarize table ignore any date range I add? I tried Datesbetween, nothing.