Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Employee
Employee

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.