Set Analysis - Current Selection with End of Month date of all previous months

[Anonymous]

  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

[Anil_Babu_Samineni]

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])

[antoniotiman]

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

[Anonymous]

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