cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Endre
New Member

sameperiodlastyear with date filter causing blank pivot table

Hi All,

 

I created a powerpivot report where sales revenue (Values) is shown for the current (2016/17) and previous (2015/16) financial year (Column Lables) for each selected product (Row Labels).

Values =sum(tblSales[Revenue])

I created relationship to a date table and added a slicer (sorted from APR to MAR) to filter the pivot table.

I also added a measure  to show the change vs. last year:

Measure = sum(tblSales[Revenue])-CALCULATE(SUM(tblSales[Revenue]),SAMEPERIODLASTYEAR(Calendar[C_Date]))

Everything works as it should: Difference is calculated perfectly with any (continuous) month periods Smiley Happy

 

Now I wanted to reproduce the whole process in a new file but as soon as I change the slicer (Calendar[C_Month]) from "All" the whole pivot table turns blank (not only the measure, even the column headers disappear). Smiley Frustrated

The only working sollution is to replace the slicer (Calendar[C_Month]) with slicer (tblSales[Month]) but it is suboptimal and sorting can not be changed (remains A-Z).

 

I have been comparing the two files but can not find the reason why the SAMEPERIODLASTYEAR function is working in one case and causes problems in the other. While there are certain changes in the new file I tried to recreate all the relations as I did in the old file. Can anyone point me in the right direction what may cause this strange behaviour?

 

Any hint would be highly appreciated,

Endre

 

1 REPLY 1
Phil_Seamark
Microsoft
Microsoft

Hi @Endre,

 

I was able to use your formula and it seemed to be working ok for me.

 

I use fields from the Calendar table in the Matrix but otherwise it behaved including using a slicer using a Month field from the Calendar table.

 

 

Are you definitely using fields from the Calendar table on the Matrix and not from the tblSales table?

 

In terms of ordering your tblSales[Month], you can add a column to tblSales that has your month in YYYYMM format (as whole number) which you can use to sort by, but like you say, this is sub-optimal

 

Any chance you can share a cut down version of your PBIX File?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.