Rolling Dates

[wnicholl]

I have dax measue that works but I beleive the dates are static and would need to be manually updated for the next months. I need the dates to automatically change;  From: DATE(2022,05,01), DATE(2023,04,30)  To  DATE(2022,06,01), DATE(2023,05,30) for the next month. Any help would be greatly appricated. 

Here is the full measure:

2023 Inforce =

CALCULATE(

    COUNTA('AISCVGP'[ACCUS#]), FILTER('AISCVGP', AISCVGP[ACSTA] = "ACT") ,

    DATESBETWEEN('Calendar'[Date], DATE(2022,05,01), DATE(2023,04,30)

))

[jdbuchanan71]

I use the VAR just to make it easier to read in this case but you could write the measure like this as well.

2023 Inforce =
CALCULATE (
    COUNTA ( 'AISCVGP'[ACCUS#] ),
    AISCVGP[ACSTA] = "ACT",
    DATESBETWEEN (
        'Calendar'[Date],
        EOMONTH ( TODAY (), -13 ) + 1,
        EOMONTH ( TODAY (), -1 )
    )
)

[jdbuchanan71 · Accepted solution]

Sorry, I misread your starting date, it is in 2022 so that would look like this which makes more sense.

2023 Inforce =
VAR _Start = EOMONTH ( TODAY(), -13 ) + 1
VAR _End = EOMONTH ( TODAY(), -1 )
RETURN
    CALCULATE (
        COUNTA ( 'AISCVGP'[ACCUS#] ), 
		AISCVGP[ACSTA] = "ACT",
        DATESBETWEEN ( 'Calendar'[Date], _Start, _End )
    )

[wnicholl]

That worked! Thank you very much. I need to learn how to use the "VAR" dax code!   Very helpful!

[jdbuchanan71]

@wnicholl 

You can use TODAY() to get a dynamic date range.  Next month should it be 
DATE(2022,06,01), DATE(2023,05,30) or
DATE(2022,06,01), DATE(2023,05,31) ?
Assuming you want the first of the month and the end of the previous month it would be like this.

2023 Inforce =
VAR _Start = EOMONTH ( TODAY (), -1 )
VAR _End = _Start + 1
RETURN
    CALCULATE (
        COUNTA ( 'AISCVGP'[ACCUS#] ), 
		AISCVGP[ACSTA] = "ACT",
        DATESBETWEEN ( 'Calendar'[Date], _End, _Start )
    )

You also don't need the FILTER('AISCVGP' portion and it is a bad idea to use FILTER over a whole table so I updated that as well.

[wnicholl]

@jdbuchanan71 

I tried the measure and it's returning no values.  Yes, the next set of dates should be DATE(2022,06,01), DATE(2023,05,31). Any other suggestions? If I use datesbetween I would need to update the dates manually...is that corrcet?  Thank you!