Count of Month

[Anonymous]

Hello all,

I need a formula that counts the number of months from a certain date, I have searched and count not find this exactly. I need to be able to count the months in the FY and our FY starts in September.

Basically need a formula that is TODAY - [09/01/2019] = 11

then in August it would equal 12 and so on.

Thanks in advance

@edhans 

[edhans · Accepted solution]

Hey @Anonymous , Try this:

COUNTROWS(
    DISTINCT(
        SELECTCOLUMNS(
            CALENDAR(
                DATE( 2019, 9, 1 ),
                TODAY()
            ),
            "Months", YEAR( [Date] ) * 100
                + MONTH( [Date] )
        )
    )
)

You should, or course, replace the DATE(2019,9,1) with something a bit more dynamic like the start of your current fiscal year.

What this does is:

1. Generates a list of dates from 9/1/2019 through today
2. Selects a column that is the combination of the year and month, so 201909, 201910, etc. (I added years in case you wanted to have more than 12 months)
3. Returns those distinct Year/Month combos
4. Counts them.

[Anonymous]

Works perfect, thank you!

[edhans]

Great. Glad it helped @Anonymous 

[jdbuchanan71]

@Anonymous 

I'm assuming you have a date table with a month-year field so it would be something like this.

Month Count = 
VAR _Today = TODAY ()
VAR _PYStart = DATE ( YEAR ( _Today ) - IF ( MONTH ( _Today ) >= 9, 0, 1), 9, 1 )
RETURN
    COUNTROWS (
        CALCULATETABLE (
            VALUES ( DATES[Month Year] ),
            DATES[Date] <= _Today && DATES[Date] >= _PYStart
        )
    )