Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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).
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
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?
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |