cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Dragon496
Frequent Visitor

SAMEPERIODLASTYEAR does not work for the final date in the table

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.

Dragon496_1-1620815209705.png

 

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?
1 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.

View solution in original post

3 REPLIES 3
daxer
Solution Sage
Solution Sage

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

Dragon496
Frequent Visitor

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

 

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.

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors