Set analysis between two date

[jmialoundama]

Hi, 

I create two variable : 

/*Current month*/

LET vCurrentMonth = MonthName(addmonths(today(),-1));
/*Next 7 months*/
Let v7NextMont= MonthName(addmonths(today(),+5));

I do this set analysis to calculate amount between current months and 7 next months : 

=Sum({<[Date_rec] = {'>=$(=vCurrentMonth)<=$(=v7NextMont)'}>}[Amount])

But it's doesn't work. 

Thanks you in advance for your help

[NitinK7]

can you add here your sample data for date and amount field

have to check your date format

[jmialoundama]

Hi @NitinK7 , 

Thanks for you reply 

I have this data : 

Date_rec	Amount
july 2022	30
july 2022	40
May 2021	10
April 2020	10
March 2022	5

Example: We are in May 2022, I need to recover the amounts from May to November 2022.

[Mark_Little]

Afternoon,

I would assume you set analysis is not work working as your variables are set to return month names. This will mean that the set analysis is trying to find a range between to string values. I.e.

Sum({<[Date_rec] = {'>=JAN<=FEB'}>}[Amount])

I believe your set analysis is correct. I would normally work with Full date on this kind of thing, so >=01/01/2022<=28/02/2022

Mark

[jmialoundama · Accepted solution]

Hi @Mark_Little, 

I change my variable like this : 

/*Mois courant*/
LET vCurrentMonth=Monthstart(addmonths(Today(),0));

/*7 prochains mois*/
Let v7NextMonth=Monthstart(addmonths(Today(),5))

I obtain this two dates : 

And my set analysis it's: 

=Sum({<[Date_rec] = {'>=$(=vCurrentMonth)<=$(=v7NextMonth)'}>}[Amount])

And it's work ! 

Thanks ! 🙂