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.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Dec 01, 2020

narday 

I'd suggest creating the PivotTable add data to data model and using Power Pivot DAX create measures which will calculate the difference of values with ones for basis date of each ID.

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

    SergeiBaklan Sounds like a plan! How do I go about creating the measure? 

     

    Im using excel 2013, so i am using calculated fields. 

     

    What will be the correct formula? 

     

    Thanks

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

      narday 

      If I understood the logic correctly we'd like to pivot like this

      If create the measure

      Value to Prev:=
VAR currentDate=MAX(Table1[Date])
VAR minDate=CALCULATE(
    MIN(Table1[Date]),
    ALL(Table1[Date])
)
VAR prevDate=CALCULATE(
   MAX(Table1[Date]),
   ALL(Table1[Date]),
   KEEPFILTERS(Table1[Date] < currentDate)
)
VAR prevValue=CALCULATE(
   MAX(Table1[Value]),
   Table1[Date]=prevDate
)
VAR currentValue=MAX(Table1[Value])
VAR Result = IF(
       currentDate=minDate,
       BLANK(),
       currentValue - prevValue
)
RETURN Result

      filtered values will be like

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

Resources