Forum Discussion

Copper Contributor

Dec 01, 2020

Solved

Pivot Table Slicer not displaying all dates

Hi guys I am using power query. the original data is aggregated, so to find values on a certain day, i have just asked the pivot table to show difference from previous item. Read below I ...

excel

SergeiBaklan

Dec 03, 2020

narday

I added calculated column

with formula

=IF(
   ISBLANK(
        CALCULATE(
              MAX(Table1[Value]),
              FILTER(ALLEXCEPT(Table1,Table1[ID]),Table1[Date]<EARLIER(Table1[Date])))
         ),
   BLANK(),
Table1[Value]-CALCULATE(
             MAX(Table1[Value]),
             FILTER(ALLEXCEPT(Table1,Table1[ID]),Table1[Date]<EARLIER(Table1[Date]))
         )
)

and add it into PivotTable

Please check if you may open the model in attached file.

PivotTable compare to previous Ver2.xlsx126 KB

narday

Copper Contributor

Dec 02, 2020

SergeiBaklan Hello Thank you for your reply,

Actually, your example above for the 2020-01-25 slicer should actually be 8 not 3, because that's the difference to date 2020-01-15. The middle table equation you did is three which is the difference. I don't think that's correct.

Also, 2020-01-25 should be 1006.

Somehow, it needs to memorise the figure and display after slicer has been implemented.

Also, your sheet doesn't work. I am using excel 2013 on professional plus...you able to convert or can you save the formula on my files?

I have added my workbook here for you to have a look at please?

Sergei.zip595 KB

SergeiBaklan

Diamond Contributor

Dec 02, 2020

narday

Sorry, but I did as in your model. If the take source data for ID=100, they are

and in your PivotTable it is shown as

2230-2196 = 34

2196-2191 = 5

Based on your comments it shall show 2230 for Nov 30, and after that difference. If you'd like to change current logic as in your file, please explain which on it shall be.

2013 and 365 files are in same format, which error do you have opening the file?

narday
Copper Contributor
Dec 03, 2020
SergeiBaklan

So sorry Sergei,
I was wrong about the calculation. Indeed, your pivot table displays the correct number. Apologies again.

Just to reiterate, for ID 101. The difference between 15/01/2020 - 25-01-2020 is 3 of which your centre table and pivot slicer presents which is correct and what I'm looking for.

Ok, now that's clear. The images attached are the errors when i try to open from the ribbon powerpivot > manage the second image is when i try to click the slicer.
- SergeiBaklan
  Diamond Contributor
  Dec 03, 2020
  narday
  
  I see, thank you. It looks like Power Pivot add-in for 2013 is very outdated, even if data models are compatible, DAX operators - not. There is no way to convert DAX syntax back, and I don't remember how to re-write DAX formula in compatible with 2013 way.
  
  Will try to play with it, but no promises.
  - narday
    Copper Contributor
    Dec 03, 2020
    No, thank you, Sergei. I've been stuck on this for many months and you're the only one who helped.
    
    Do you know how i upgrade to 2013 power pivot?
    
    Ive added you via linked in...