Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

In a summarize function, the date range is being ignored

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
6 REPLIES 6
Anonymous
Not applicable

Hi @Anonymous ,

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

1.Create the simple table.

vjiewumsft_0-1709779314757.png

vjiewumsft_1-1709779455040.png

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.

vjiewumsft_2-1709779516696.png

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

 

vjiewumsft_3-1709779538934.png

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.

 

 

 

Anonymous
Not applicable

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
Super User
Super User

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

Anonymous
Not applicable

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?


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


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.

MRUry7_0-1709838348556.png

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
Super User
Super User

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

Anonymous
Not applicable

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.

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.