Only sum a field when its the month of today

[Applicable88]

Hello,

I have following table:

Date	Productive Days	Revenue
11.09.2020	1	1215
12.09.2020	1	1215
13.09.2020	1	8555
05.10.2020	1	2564
06.10.2020	1	5874
07.10.2020	1	9652
08.10.2020	1	7423
09.10.2020	1	3652
10.10.2020	1	8545

The date field is formatted as date.  

Now I only want to sum the productive dates (non vacation days) always of the current month. 

If I need an KPI window to show me all working days of the current month I tried to use this expression:

sum(if(num(year(Date)) & Num(Month(Date)) = num(year(today())) & num(month(today))) Productive Days)

So basically I'm converting all the date values and also the current month and year values into a number. It works pretty well when I do it separtely in another kpi window, each function for itself. But after putting them all together it doesn't return me the  sum of all Productive days where the condition is actually met. 

Have someone an idea whats the problem here, or is there a better way to express what I'm looking for?

Thanks in advance. 

[Taoufiq_Zarra · Accepted solution]

@Applicable88  are you looking for ?

=Sum({$<Date={"= (Year(Date) = year(today()) and Month(Date) = Month(today())) "}>}[Productive Days])

[Kushal_Chawda · Accepted solution]

@Applicable88  try below

=Sum({$<Date={">=$(=date(monthstart(today())))<=$(=date(monthend(today()))))"}>}[Productive Days])

[Applicable88]

@Taoufiq_Zarra as always great! 

Thx. 

[Ari_vzla]

Why this answer is not working for me? I am replaced Date and Productive Days with my fields but system show me an error

=Sum({$<[Tiempo extra 2020.Mes]={"= (Year(Date) = year(today()) and Month(Date) = Month(today())) "}>}[[Total Hrs Extras]])

I am trying to do the same. Instead Date  is Mes and instead productived days is otal Hrs Extras

Thank you 

Arieli

[Applicable88]

Hi @Ari_vzla , 

your last [Total Hrs Extras] has to square brackets. If you remove one of them does it solve the problem?

Best.