5 Replies Latest reply: Feb 10, 2018 8:48 PM by Sunny Talwar RSS

    Pivot Table measure issue

    Elie Issa

      Dear All,

       

      I am using the below expression in my pivot table :

       

      Count({

      < [Transfer In] = {'Yes'},

        [Joining Date]={"$(= '>=' & date($(vBOMFilterDate) + 1) & '<=' & date($(vEOMFilterDate)))"}

      >

      }Distinct [Assignment Number])

       

       

      vBOMFilterDate :

      =RangeMax(

      Min(All{<Year=P(Year),Quarter=P(Quarter),[Month Year]=P([Month Year]),[Month Num]=P([Month Num]),Month=P(Month),Day=P(Day)>}Num([Master Calendar Date]))

      ,yearstart($(vEOMFilterDate)))

       

      vEOMFilterDate:

      = Max(All{<Year=P(Year),Quarter=P(Quarter),[Month Year]=P([Month Year]),[Month Num]=P([Month Num]),Month=P(Month),Day=P(Day)>}Num([Master Calendar Date]))

       

      when opening the pivot table i am getting 0 for all years because the filters above will be :

       

      vBOMFilterDate : 1/1/2018

      vEOMFilterDate : 7/2/2018 (today's date)

       

       

      when choosing a specific year we are getting the exact numbers.

       

      What could be the solution in the pivot to make the measure working for all years even if we didn't choose any specific year.

       

      Your urgent support is needed thank you.

       

       

      ali_hijazi