SAMEPERIODLASTYEAR does not work for the final date in the table

[Dragon496]

I have a dashboard where I am have 6 pie charts. The first 3 display data, filtered by a date slicer (there is a second slicer, based on another field but this did not come into the scenario I am about to describe).

The first three pie charts, display the correct data.

The 2nd row of pie charts, showed the same data but for one year ago.

I have a date dimension table, with the relationship correctly set up, so the date slicer works.

For the second row, of pie charts, I am using the SAMEPERIODLASTYEAR, such as:-

Incident Count Last Year =

CALCULATE(

COUNTROWS(careSectorIncident),

SAMEPERIODLASTYEAR(Date_Dimension[Date])

)

The problem is, the above formula works for every date, as far as I can see, except when the date range includes the last date, for which there is data.

For example, if I change the date range to 01/05/2021-10/05/2021, the total on each pie chart, for the previous year, is 20, which is correct. If I amend the last date to include 11/05 the total count on each pie chart is 100. I have tested this on several days and have proven the problem only occurs when you include the last date for which the data (when I originally developed this last Thursday, the problem date was 06/05.

Suggest why this is happening?

[Anonymous]

A very common issue... Please read this: Hiding future dates for calculations in DAX - SQLBI

[Dragon496]

Thanks for that. Your solution does fix my issue but I am confused why, since I am already filtering my date dimension to exclude future dates.

Sure enough, if I remove the filter from my date dimension table, the problem relating to same period last year, is resolved. I have even removed the calculated field, which determines whether the date exists in my fact table (and from the measure) and it still works.

However, we also use that date in a dimension table. We were filtering date dimension table, to restrict the dates in the slicer to the dates in the fact table.

In conclusion, it would appear that we require the following:-

• the date dimension table to include the dates up to a year after the maximum fact table date
• do we also need to have at least a year prior to the minimum date?
• If we do the above, how can we reduce the date range in our slicer?

Thanks

[Anonymous · Accepted solution]

Yes, a proper data table which you any decent model must have consist of dates covering full years that you encounter in your model. No, you don't have to go as far back as one year before the first date in your model. You just have to cover the years you do have in your fact tables without any gaps in between. How to reduce entries in a slicer? Easy. You create a suitable measure that will filter your slicer entries  (think: Filter Pane) accordingly to whatever criteria you want.