How to calculate previous months data when selecting current month ?

[JishnuGS]

Hi Team,

I want to populate the pervious 3 months data when I'm selecting a month name.

Paid Month Long Name is my filter. For example if I select April 2020, I need to get the Sum(POCA) of March 2020, February 2020 and January 2020.

Could you please help me with the query?

Thanks,
Jishnu 

[Taoufiq_Zarra · Accepted solution]

@JishnuGS  Maye be like this ?

=Sum({1<[Paid Month Long Name] ={">$(=addmonths(max([Paid Month Long Name]),-3))<$(=max([Paid Month Long Name])) "}>} POCA)

Make sure that

SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

for example :

Sample:
LOAD [Paid Month ID],Date#([Paid Month Long Name],'MMMM YYYY') as [Paid Month Long Name],Date(Date#([Paid Month Long Name],'MMMM YYYY')) as [Paid Month Date], POCA INLINE [
    Paid Month ID,Paid Month Long Name, POCA
    1, April 2020,1
    2, March 2020,2
     3, March 2019,3
    
];

output:

[JishnuGS]

Hi @Taoufiq_Zarra ,

Thank you!

I'm getting the values now. 
But how can we eliminate the below highlighted values. I want pervious 3 month data, previous 1 is working fine but in previous 2 adding the previous 1 value also. so on

[Taoufiq_Zarra]

@JishnuGS  I think its correct

between date not equal

[JishnuGS]

@Taoufiq_Zarra not equal  is not working 

[JishnuGS · Accepted solution]

Hi @Taoufiq_Zarra ,

To get only the single value in the column  

Pervious 1st month
Sum({1<[Paid Month Long Name] ={">$(=addmonths(max([Paid Month Long Name]),-2))<$(=max([Paid Month Long Name])) "}>} POCA)

Pervious 2nd month
Sum({1<[Paid Month Long Name] ={">$(=addmonths(max([Paid Month Long Name]),-3))<$(=max([Paid Month Long Name])-32) "}>} POCA)

Pervious 3rd month
Sum({1<[Paid Month Long Name] ={">$(=addmonths(max([Paid Month Long Name]),-4))<$(=max([Paid Month Long Name])-63) "}>} POCA)

Thank you !!!!!