Forum Discussion

narday's avatar
narday
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's avatar
    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.

narday's avatar
narday
Copper Contributor
Dec 03, 2020
hmm, i have 2013, but i need 2016? Youre using 2016?

How difficult would it be to convert the dax?
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
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.

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Dec 04, 2020

    narday 

    Great to know, thank you for sharing. I'm quite seldom on LinkedIn, will do when be next time.

  • narday's avatar
    narday
    Copper Contributor
    Dec 04, 2020

    SergeiBaklan Sergei! It worked! haha! I just rewrote it using the 'tab' on keyboard.

     

    It works! I cant believe it!

     

    GENIUS!

     

    Please accept my invite on linkedin

  • narday's avatar
    narday
    Copper Contributor
    Dec 04, 2020

    SergeiBaklan Hey, sorry for delay in responding, been working on this.

     

    Ok, the work book works 😄 AND i have attempted to insert the formula. image 1 will show this.

     

    Image 2 shows a query error? Im not sure what this is?

     

    Could you help? Im nearly there Sergei i believe, i really appreciate your help.

     

    thanks

Resources