3MM From Static Date in The Past

[Anonymous]

Hello PB Team,

I have a task to build a calculation that counts the number of responses every 3 months from the 20th day of each month + 3 months. For example, if today is the 8th of February, it must count the previous period: January 20th, 2021 - October 20th, 2020 but if today is February 20, it must show me February 20th, 2021 - November 20th, 2020, and so on like this:

I build a variable but somehow the 'VAR EndDateBefore20' doesn't work and I can't find an alternative. Please Help. Thank you.

Period 3MM =
VAR StartDateBefore20 = EDATE (STARTOFMONTH('Calendar'[Date])+19, -4)
VAR EndDateBefore20 = EDATE (STARTOFMONTH('Calendar'[Date])+19, -1)
VAR StartDateAfter20 = EDATE (STARTOFMONTH('Calendar'[Date])+19, -3)
VAR EndDateAfter20 = EDATE (STARTOFMONTH('Calendar'[Date])+19, 0)

RETURN
IF(DAY(TODAY())<20,CALCULATE([# Responses],CALCULATETABLE(
'Calendar',
FILTER (
'Calendar',
'Calendar'[Date] >= StartDateBefore20
&& 'Calendar'[Date] <= EndDateBefore20
)))
,
IF(DAY(TODAY())>20,CALCULATE ([# Responses],CALCULATETABLE(
'Calendar',
FILTER (
'Calendar',
'Calendar'[Date] >= StartDateAfter20
&& 'Calendar'[Date] <= EndDateAfter20
))),
BLANK()
))

Please, help.
Thank you very much.

[amitchandak · Accepted solution]

@Anonymous , You can get start and end date of your period like

Var EndDate = eomonth('Calendar'[Date]-19,-1)+20
Var startdate = date(year(EndDate), month(EndDate)-3, day(EndDate))

and can do rest of calculation on these

[Anonymous]

@amitchandak Thank you very much! Somehow the combination of eomonth('Calendar'[Date] doesn't work in a measure, so I combined it with EndOfMonth. So the result is:

LSP 3MM -1 = 
VAR EndDateLess20 = EOMONTH(ENDOFMONTH('Calendar'[Date])-19,-1)+20
VAR StartDateLess20 = DATE(YEAR(EndDateLess20), MONTH(EndDateLess20)-3, DAY(EndDateLess20))
VAR EndDateMore20 = EOMONTH(ENDOFMONTH('Calendar'[Date])-19,0)+20
VAR StartDateMore20 = DATE(YEAR(EndDateMore20), MONTH(EndDateMore20)-3, DAY(EndDateMore20))

RETURN
    IF(DAY(TODAY())<=20,CALCULATE([Channel NPS],CALCULATETABLE(
            'Calendar',
            FILTER (
                'Calendar',
                'Calendar'[Date] > StartDateLess20
                    && 'Calendar'[Date] <= EndDateLess20
            )))
,
     CALCULATE ([Channel NPS],CALCULATETABLE(
            'Calendar',
            FILTER (
                'Calendar',
                'Calendar'[Date] > StartDateMore20
                    && 'Calendar'[Date] <= EndDateMore20
            ))))