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.

Resources