Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
When I am making no selection the values are correct but when I use any filter or select any dimension from pivot table for filtering the values get incorrect.
The same expression and variables works fine on a straight table but incorrect on pivot.
Can Someone please help in this scenario !!!!!!!
Can you share your expressions and screenshots.
num(Sum({<MonthYear=,CalenderDate={">=$(=Date(MonthStart(AddMonths(Max(CalenderDate),-2)), 'DD/MM/YYYY'))<=$(=Date(MonthStart(AddMonths(Max(CalenderDate),0)), 'DD/MM/YYYY'))"}>}[Units])/3,'##0.00')
This is the expression for last 3 months and similarly for 6,12 months.Basically I am getting 9 times the value on selecting a particular filter.
Can you ignore MonthYear in your calendardate calculation like this:
num(Sum({<MonthYear=,CalenderDate={">=$(=Date(MonthStart(AddMonths(Max({<MonthYear=>}CalenderDate),-2)), 'DD/MM/YYYY'))<=$(=Date(MonthStart(AddMonths(Max({<MonthYear=>}CalenderDate),0)), 'DD/MM/YYYY'))"}>}[Units])/3,'##0.00')