Capturing Correct Date Range in Power BI Summary Table Calculated column Based on Slicer date

[nuraneesa]

Dear Power BI community,

I'm facing an issue with capturing the correct date range in my Power BI summary measure based on the date slicer on the dashboard. I've tried multiple approaches, but the measure is not reflecting the selected date range accurately.

Here's the background:

• I have a date slicer on the dashboard that filters the InfoCalendarDate table.
• I have a related eTMS table that contains the data I want to summarize, with a date field called InfoDate.1.
• I've tried creating measures for the start and end dates using CALCULATE with ALLSELECTED, as well as a calculated table approach, but the summary table is not respecting the date slicer selection. it still taking the min and max of the eTMS table. 
• On the visual board, as measure, it calculate correctly based on date. But as my end goal to put the OTGroup as a row in matrix table, i need it go be in calculated column. 
  
  
  "SummaryOTGroup =

  VAR MinDate = MINX(ALLSELECTED('InfoDateCalendar'), 'InfoDateCalendar'[Date])

  VAR MaxDate = MAXX(ALLSELECTED('InfoDateCalendar'), 'InfoDateCalendar'[Date])

  RETURN

  SUMMARIZE(

      CALCULATETABLE(

          'eTMS',

          'eTMS'[InfoDate.1] >= MinDate && 'eTMS'[InfoDate.1] <= MaxDate

      ),

      'eTMS'[PersonId],

      'eTMS'[Name],

      'eTMS'[Dept],

      "Total Overtime", [TotalOvertime],

      "Total Forecast OT", [AutoOTForecast],

      "Estimate Total OT", [TotalOvertime] + [AutoOTForecast],

      "Date Range", FORMAT(MinDate, "MM/dd/yyyy") & " - " & FORMAT(MaxDate, "MM/dd/yyyy")

  )"
  
  Could you please help me understand the best way to capture the correct date range in my summary measure based on the date slicer? I'm happy to provide more details about my data model and the specific measures I've tried.
  
  

[Anonymous]

Hi, Wilson_ and johnt75 ,thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.

Hello,@nuraneesa .I am glad to help you.
I agree with johnt75  .

The properties of the calculated table/column are static, it cannot interact with the filters (slicers visual) on the visualization page, so it cannot satisfy the need for dynamism.

Measures, on the other hand, can be interacted with dynamically, but they can only return a single value.

Neither calculate columns nor calculated tables can return tables dynamically.

Therefore, if you need to solve the dynamic requirements, you can use the measure to realize indirectly(not calculate columns/tables)
URL:

Table Filter does not work based on slicer selecti... - Microsoft Fabric Community

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[johnt75]

Calculated tables are only calculated during data refresh, they do not pay any attention to filters or slicers. If you want something to react dynamically, you have to create a measure.

[Wilson_]

Can you please share a sample pbix file? (If you don't know how, please check the pinned thread in the forum.) It would make debugging your issue easier. 🙂