cancel
Showing results for
Did you mean:
Highlighted
Post Patron

## Count of Month

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User II

## Re: Count of Month

Hey @dgp281 , 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.

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
4 REPLIES 4
Highlighted
Super User IV

## Re: Count of Month

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
)
)
Highlighted
Super User II

## Re: Count of Month

Hey @dgp281 , 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.

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
Highlighted
Post Patron

## Re: Count of Month

Works perfect, thank you!

Highlighted
Super User II

## Re: Count of Month

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting

Announcements

#### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### July 2020 Community Highlights

Learn about the exciting things that happened in July.

#### Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

#### Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.