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‌

[ali_hijazi]

so you mean you need to aggregate your expression by year

sum(aggr(count(......),Year))

[elie_issa]

I tried the below and still not working :

sum(aggr(

Count({

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

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

>

}Distinct [Assignment Number],Year)))

I think the main issue is that the Dynamic dates are not changing correctly and always showing the latest year.

Any idea if we have other solution.

[sunny_talwar]

Would you be able to elaborate on what dimensions you have in the chart where you are using this?

[elie_issa]

it's not related to dimension because first i was trying to use it in a simple table with only the Year Dimension and still have wrong figures.

[sunny_talwar]

Not 100% sure, but try this

Sum({<[Transfer In] = {'Yes'}, [Joining Date]={"$(= '>=' & date($(vBOMFilterDate) + 1) & '<=' & date($(vEOMFilterDate)))"}>} Aggr(Count({<[Transfer In] = {'Yes'}, [Joining Date]={"$(= '>=' & date($(vBOMFilterDate) + 1) & '<=' & date($(vEOMFilterDate)))"}>} DISTINCT [Assignment Number],Year)))