Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
What we are looking for is , Pivot chart should display the selected date plus the EOM date of previous months
If we have the dataset as below :
Load Date Credit Rate
05/01/2017 720
05/02/2017 710
05/03/2017 680
05/04/2017 540
04/04/2017 800
04/01/2017 820
03/31/2017 740
01/17/2017 728
01/28/2017 852
01/31/2017 738
Filter as below and the user selected "04/04/2017" from the filter
05/01/2017
05/02/2017
05/03/2017
05/04/2017
04/04/2017
04/01/2017
03/31/2017
01/17/2017
01/31/2017
Pivot chart should display as below (the selected date plus the End of the Month date of previous months)
Load Date Credit Rate
04/04/2017 800
03/31/2017 740
01/31/2017 738
Can you create one flag and use below expression. I am not testing now. But try from your end
Load *, if([Load Date]=Date(Floor(MonthEnd([Load Date]))),1,0) as MonthEnd_Flag Inline [
....
....
];
=sum({<MonthEnd_Flag = {1}, [Load Date]={$(=concat(chr(39) & [Load Date] & chr(39),',') )}>}[Credit Rate])
Hi,
try this Expression
If(Only({<[Load Date]={'<=$(=Max([Load Date]))'}>} [Load Date]) = Date(Floor(MonthEnd(Only({<[Load Date]={'<=$(=Max([Load Date]))'}>} [Load Date]))))
or [Load Date]=Max([Load Date]),Sum({<[Load Date]={'<=$(=Max([Load Date]))'}>} ([Credit Rate])))
Regards,
Antonio
Thanks to everyone for the support.
I have resolved this issue using an workaround.
I have Used Group By Function at the load script level to get the Max(Load Date) for every month