cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Calculated Formula to count records within own month

I'm trying to count the number of records within a month and put it in a calculated column so I can use it in another column based calculation.

 

Basically, I need to get the count like this:

 

 Capture.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Champion
Community Champion

Re: Calculated Formula to count records within own month

@fmx

 

Hello, try with this in a calculated column

 

Recordsinthemonth = 
VAR MonthinRow=MONTH(Table1[Date])
RETURN
COUNTROWS(FILTER(All(Table1),MONTH(Table1[Date])=MonthinRow))

Regards

 

Victor

Lima - Peru




Lima - Peru

View solution in original post

3 REPLIES 3
Highlighted
Community Champion
Community Champion

Re: Calculated Formula to count records within own month

@fmx

 

Hello, try with this in a calculated column

 

Recordsinthemonth = 
VAR MonthinRow=MONTH(Table1[Date])
RETURN
COUNTROWS(FILTER(All(Table1),MONTH(Table1[Date])=MonthinRow))

Regards

 

Victor

Lima - Peru




Lima - Peru

View solution in original post

Highlighted
Helper I
Helper I

Re: Calculated Formula to count records within own month

That worked so easily. I just started learning DAX and all the solutions I could think of were within its own bounds, ironically, like my problem here.

Highlighted
Continued Contributor
Continued Contributor

Re: Calculated Formula to count records within own month

Hello,
Since you just started learning DAX it's a good idea to be able to think about different solutions to your problems. In case if you are in that pre-variables DAX environment  SSAS 2012 or Excel < 2016 here is the solution to question with EARLIER()

 

Count =
COUNTROWS(
    FILTER(
        Table1,
        MONTH(Table1[Date]) = MONTH(EARLIER(Table1[Date]))
    )
)

Nick -

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors