topic Re: DATESINPERIOD - filter context handling with Page Date slicer, do I need a CALCULATETABLE? in DAX Commands and Tips

DATESINPERIOD - filter context handling with Page Date slicer, do I need a CALCULATETABLE?

rpiboy_1 — Tue, 27 Aug 2024 00:42:19 GMT

I have a well formed Date Table (thanks Bravo).
I have a report page with a slicer on it that allows the user to define a date range (BETWEEN)
I'm calculating rolling average as per SQLBI

Logins R6M = VAR _noMonths = 6 VAR _lastSelectedDate = MAX('Date'[Date]) VAR _period = DATESINPERIOD( 'Date'[Date], _lastSelectedDate, -_noMonths, MONTH) VAR _result = CALCULATE( AVERAGEX( VALUES('Date'[Fiscal Month Number]), [Logins (sum)] ), _period ) VAR _lastDateWithLogin = MAX( 'Monthly User Activity by Project'[Date]) VAR _firstVisibleDate = MIN('Date'[Date]) RETURN IF( _firstVisibleDate <= _lastDateWithLogin, _result)

I know that DATESINPERIOD is 'sugar syntax' around DAX that I could write myself.
- what I don't recall, does DATESINPERIOD apply a CALCULATETABLE to the 'Date' table?
That is, if the period on the page (thanks to the slicer) started at Jan 1, 2022, will the calculation for 1/1/2022 account for the six months of 2021 that are not included in the slicer range?
I'm asking because I was thinking that the context from the slicer on page would be strictly applied, in which case the first period of averages would be in-accurate because it would be missing data.
I wrote a version of the above including a CALCULATETABLE, but both measures report the same data.

Logins R6M (filter) = VAR _noMonths = 6 VAR _lastSelectedDate = MAX('Date'[Date]) VAR _period = CALCULATETABLE( DATESINPERIOD( 'Date'[Date], _lastSelectedDate, -_noMonths, MONTH ), ALL('Date'[Date]) ) VAR _result = CALCULATE( AVERAGEX( VALUES('Date'[Fiscal Month Number]), [Logins (sum)] ), _period ) VAR _lastDateWithLogin = MAX( 'Monthly User Activity by Project'[Date]) VAR _firstVisibleDate = MIN('Date'[Date]) RETURN IF( _firstVisibleDate <= _lastDateWithLogin, _result)

Re: DATESINPERIOD - filter context handling with Page Date slicer, do I need a CALCULATETABLE?

ahadkarimi — Tue, 27 Aug 2024 01:00:13 GMT

Hi rpiboy_1,

Yes, DATESINPERIOD respects the slicer context, so it won't include dates outside the slicer range, which might cause inaccuracies. Your version with CALCULATETABLE and ALL('Date'[Date]) is better because it ensures all needed dates are considered for accurate rolling averages.

Did I answer your question? If so, please mark my post as the solution!✔️
Your Kudos are much appreciated! Proud to be a Responsive Resident!

Re: DATESINPERIOD - filter context handling with Page Date slicer, do I need a CALCULATETABLE?

rpiboy_1 — Tue, 27 Aug 2024 10:50:17 GMT

If my logic is correct, that is I do need to extend the period outside the scope of the Page Slicer, then why do both measures return the exact same values for the first dates shown. There should be at least some deviation between the two measures for the first 'period', eventually coming into alignment the further away you get from the 'start'.