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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.