DATESINPERIOD values don't match on most recent Year period

[rpiboy_1]

I have implemented the following DATESINPERIOD pattern:

 Design Area Avg Previous Period (converted) =    
    SWITCH(
        TRUE(),
        ISINSCOPE( 'Date'[Date] ),
            CALCULATE( [Design Area Avg (sum)] , DATEADD( 'Date'[Date], -1, DAY )),
        ISINSCOPE( 'Date'[Fiscal Month] ),
            CALCULATE( [Design Area Avg (sum)], DATEADD( 'Date'[Date], -1, MONTH )),
        ISINSCOPE( 'Date'[Fiscal Quarter] ),
            CALCULATE( [Design Area Avg (sum)] , DATEADD( 'Date'[Date], -1, QUARTER )),
        ISINSCOPE( 'Date'[Fiscal Year] ),
            CALCULATE( [Design Area Avg (sum)] , DATEADD( 'Date'[Date], -1, YEAR ))
    )

Except that in the most recent Year period we can see that the correct value for the previous year is not reported. This is consistent for two different measures, following the same pattern.

My hypothesis (that I'm looking for confirmation of) is that the previous period result for the current year matches the 'exact' period. That is in the context of FY24 for FY23 it is calculating from August 28th, it is not calculating for the entire period of '23.

Is that correct or is there something else going on that I'm either forgotten about or don't know?

[OwenAuger · Accepted solution]

Hi @rpiboy_1 

Yes, your hypothesis is correct, if I have understood you correctly 🙂

To describe it in more detail:

• Time intelligence functions are computed in the context of dates that actually exist in the 'Date' table (that are visible in the filter context where the function is called).
• So if a partial month/quarter/year is present in the 'Date' table, then any time intelligence functions will operate on those partial periods.
• For example, if we assume that FY is equivalent to calendar year (not sure if that's correct in your case) and the 'Date' table ends at 2024-08-28, then a filter of
  'Date'[Fiscal Year] = "FY 2024"
  is equivalent to
  DATESBETWEEN ( 'Date'[Date], dt"2024-01-01", dt"2024-08-28" )
• Therefore in the context of the filter 'Date'[Fiscal Year] = "FY 2024"
  DATEADD ( 'Date'[Date], -1, YEAR )
  evaluates to
  DATESBETWEEN ( 'Date'[Date], dt"2023-01-01", dt"2023-08-28" )

Because of this behaviour, your 'Date' table should generally include complete years (however years are defined) spanning your required date range.

https://dax.guide/functions/time-intelligence/ 

If some time intelligence need to be based on partial periods, then there are some examples of handling this here:

https://www.daxpatterns.com/standard-time-related-calculations/

Regards

[rpiboy_1]

Thanks for confirming. I also did confirm when I manipulated the Date Slicer on the report (duh!). This ultimatly leads to another question which at the moment remains un-resolved. Is it worth going about adding to the measure to automatically calculated the correct overall period, regardless of the presence of a slicer?

In my case I have a date between slicer so the end users can 'narrow' their focus. This of course is what through the DATESINPERIOD measure off. Therefore, should I consider calculating the full year, quarter and month span of the 'latest' of each so that the previous period is complete?

This is really more of a UX question, though I'm also a bit uncertain the best technical approach.

I took a look at 'end of year' and 'end of month' etc. in dax.guide, and I wasn't entirely certain of the best way to go about getting the right data to pass to the DATEADD function. I'm confident the technical aspect could be resolved, but this leaves oustanding the question of 'should it be', or should I simply ensure Report consumers are educated as to what to expect when they do manipulate the Date Slicer?

[OwenAuger]

Just realised I didn't get back to you on this one 🙂

As far as the UX question, some sort of conditional formatting (e.g. background colour) might be a good way of flagging partial periods. This would require a measure with logic to detect partial periods.

To convert partial to full periods, take a look at PARALLELPERIOD which would be my go-to function. It expands a given date range to complete months/quarters/years and shifts if needed. You may want to combine it with DATEADD or other functions.