IGNORE PREVIOUS MONTH DATA

[inam]

Hello Community,

i have a column [Opportunity ID]. with the help of =count ([Opportunity ID] ) i am creating MTD Achieved  and i am taking 2 month data.  (previous month & current month (july to today-1 ) today is 11 aug so i have data 1ST july to 10 august ) & in my dashboard i am taking last 4 date data see below .

so my question is if today is 2nd august so i want current month MTD Achieved (not july month)

If TODAY DATE IS 2 AUGUST
c product	29-07-2023	30-07-2023	31-07-2023	01-08-2023	MTD Achieved
a	8	6	5	5	Here I want only aug month =count([Opportunity ID] )
b	5	47	36	10	Here I want only aug month =count([Opportunity ID] )
c	9	100	56	6	Here I want only aug month =count([Opportunity ID] )
IF TODAY DATE IS 3 AUGUST
c product	30-07-2023	31-07-2023	01-08-2023	02-08-2023	MTD Achieved
a	6	5	5	25	Here I want only aug month =count([Opportunity ID] )
b	47	36	10	19	Here I want only aug month =count([Opportunity ID] )
c	100	56	6	4	Here I want only aug month =count([Opportunity ID] )

1ST AUG all working fine because i have today-1 data so in 1st august i have data 1st july to 31st july  so MTD Achieved count properly  only july month.  but 2nd august i have data 1st july to 1st august so here is taking 1st july to 1st august MTD  but i want only currrent month (ONLY AUG MONTH MTD ). 

[Tanish]

Hi Inam,

Try this 

Count({<Date =  {" >=$(=Monthstart(Max(Date_field))) <= $(=Max(Date_field))"}  >}[Opportunity ID] )

Hope this will Help...

[inam]

hello @Tanish  Thanks for the reply

i using your code but not getting value. & i am also used below formula but not getting value can you please correct me.

=IF(Day(Today()) = 1,
Num(COUNT({<[Date Created] = {">=$(=Floor(MonthStart(Date(Max(Today() - 1)))))<=$(=Floor(MonthStart(Today() - 1)))"}>} [Opportunity ID])),
Num(COUNT({<[Date Created] = {">=$(=Date(MonthStart(Max(Today())), 'DD-MM-YYYY'))))<=$(=DATE(Today(),'DD-MM-YYYY')"}>} [Opportunity ID])))

[Tanish]

Hi Inam,

1. If you use Today function your Date filter will not work, you need to use Max of your Date field instead of Today. For Example - Max(Date)

2. In this formula

'Num(COUNT({<[Date Created] = {">=$(=Floor(MonthStart(Date(Max(Today() - 1)))))

<=$(=Floor(MonthStart(Today() - 1)))"}>} [Opportunity ID])),

I think these both would be giving same answer and also in the number.

Is your Date fields is also in the num format. If not then it is wrong.

Here is the script we use 

Sum({<Date = {">=$(vMTD_start)<=$(vMaxDate)"}>}Amount)

Variable 

vMTD_start

=(Monthstart(Max(Date)))

vMaxDate

=Max(Date)

[inam]

which formula should i use ? i want count ([Opportunity ID] )  for current month and for date  i have column "Date Created" DD-MM-YYYY.....Please Can you give me proper expression.

[Tanish]

Here is the script we use 

1.

Count({<"Date Created"  = {">=$(vMTD_start)<=$(vMaxDate)"}>}[Opportunity ID])

Try this out.....

[inam]

hello @Tanish thanks for the help  i am using different code for MTD and its working fine(for MTD i am using this code:- =IF(Day(now())=1,
Count([Opportunity ID]),
Count({< [Date Created]= {">=$(=Date(MonthStart(MAX(Today())),'DD-MM-YYYY'))<=$(=Date(Today()-1,'DD-MM-YYYY'))"} >} [Opportunity ID]))) its working fine now

but i have one  other doubt.

i am taking 4 expression for calculate last 4 date data. see below code

=if(day(Today())=1,
Num((Count({< Floor_DC = {"=floor(Date(MonthEnd(Now()-1)-3,'DD-MM-YYYY')) "}>} PROD_TYPE)),'##'),
Num((Count({< Floor_DC = {">=$(=((floor(date(Max(Now()- 4)))))) <=$(=(floor(Date(Now()-4))))"}>} PROD_TYPE)),'##'))

=if(day(Today())=1,
Num((Count({< Floor_DC = {"=floor(Date(MonthEnd(Now()-1)-2,'DD-MM-YYYY')) "}>} PROD_TYPE)),'##'),
Num((Count({< Floor_DC = {">=$(=((floor(date(Max(Now()- 3)))))) <=$(=(floor(Date(Now()-3))))"}>} PROD_TYPE)),'##'))

=if(day(today())=1,
Num((Count({< Floor_DC = {"=floor(Date(MonthEnd(Now()-1)-1,'DD-MM-YYYY')) "}>} PROD_TYPE)),'##'),
Num((Count({< Floor_DC = {">=$(=((floor(date(Max(Now()- 2)))))) <=$(=(floor(Date(Now()-2))))"}>} PROD_TYPE)),'##'))

=if(day(today())=1,
Num((Count({< Floor_DC = {"=floor(date(MonthEnd(Now()-1),'DD-MM-YYYY')) "}>} PROD_TYPE)),'##'),
Num((Count({< Floor_DC = {">=$(=((floor(date(Max(Now()- 1)))))) <=$(=(floor(Date(Now()-1))))"}>} PROD_TYPE)),'##'))

and its working fine if the date is 1st august is show last 4 date data.

but i want if the date is 2 august is show only current month date data other colum shows '0' see below example

If TODAY DATE IS 2 AUGUST
c product	29-07-2023	30-07-2023	31-07-2023	01-08-2023
a	0	0	0	5
b	0	0	0	10
c	0	0	0	6