cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Patron
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.

 

 

Thanks in advance

@edhans 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User II
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 I answer your question? Mark my post as a solution!
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

View solution in original post

4 REPLIES 4
Highlighted
Super User IV
Super User IV

Re: Count of Month

@dgp281 

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
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 I answer your question? Mark my post as a solution!
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

View solution in original post

Highlighted
Post Patron
Post Patron

Re: Count of Month

Works perfect, thank you!

Highlighted
Super User II
Super User II

Re: Count of Month

Great. Glad it helped @dgp281 



Did I answer your question? Mark my post as a solution!
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

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

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

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

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

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.

Power BI Desktop August 2020 Update

Power BI Desktop August 2020 Update

We have great updates this month! Click the link for the video with more info.

Top Solution Authors
Top Kudoed Authors