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 have created a pivot table (image 1)

 

that pivot table displays email sends per campaign id

 

the values in the pivot table are based as 'difference from' date and 'base item' previous (image 2)

 

When i insert a slicer, the dates disappear because its looking at the previous item, which has been removed by the slicer (image 3)

 

How do i go about solving this issue please 🙂

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.

14 Replies

Sort By
  • 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

Resources