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.

Resources