cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
steve_geist
Frequent Visitor

DAX: Evaluate column of dates and return the greatest count of events within any month within range

DAX: Evaluate column dates and return the greatest count of events within any month within range

Hi, I am trying to write DAX for a stand-alone measure that will evaluate a column of event dates in a table , and return the greatest count of events (including duplicates) in any month contained within the column of dates. (I need to know the greatest number of events to have ever occurred within any month within the column of dates.)  I know a matrix visual would accomplish this, but I need the singlular output value for a Card visual.  I think this should utilize some combination of MAXX and CALCULATE but I am lost 😞


Example data below, the measure should return "18" (corresponding to April 2020).    THANKS!

 

Event Date
1/1/2020
1/2/2020
1/4/2020
1/6/2020
1/9/2020
1/11/2020
1/15/2020
1/17/2020
1/20/2020
1/25/2020
1/29/2020
1/30/2020
1/31/2020
2/4/2020
2/8/2020
2/11/2020
2/16/2020
2/19/2020
2/24/2020
2/27/2020
2/29/2020
3/5/2020
3/7/2020
3/9/2020
3/10/2020
3/15/2020
3/18/2020
3/22/2020
3/27/2020
4/1/2020
4/2/2020
4/3/2020
4/4/2020
4/5/2020
4/6/2020
4/6/2020
4/11/2020
4/15/2020
4/15/2020
4/22/2020
4/23/2020
4/25/2020
4/26/2020
4/27/2020
4/28/2020
4/29/2020
4/30/2020
5/10/2020
5/12/2020
5/16/2020
5/19/2020
5/24/2020
5/28/2020
6/1/2020
6/4/2020
6/9/2020
6/13/2020
6/17/2020
6/20/2020
6/23/2020
6/24/2020
6/29/2020
7/3/2020
7/8/2020
7/10/2020
7/15/2020
7/17/2020
7/21/2020
7/23/2020
7/28/2020
8/1/2020
8/2/2020
8/7/2020
8/8/2020
8/11/2020
8/16/2020
8/19/2020
8/20/2020
8/25/2020
8/29/2020
8/30/2020
8/31/2020
9/2/2020
9/3/2020
9/7/2020
9/10/2020
9/11/2020
9/15/2020
9/20/2020
9/21/2020
9/26/2020
10/1/2020
10/3/2020
10/7/2020
10/8/2020
10/11/2020
10/12/2020
10/17/2020
10/18/2020
10/20/2020
10/22/2020
10/26/2020
10/31/2020
11/3/2020
11/4/2020
11/9/2020
11/10/2020
11/15/2020
11/17/2020
11/21/2020
11/26/2020
11/29/2020
12/2/2020
12/7/2020
12/9/2020
12/13/2020
12/16/2020
12/18/2020
12/20/2020
12/24/2020
12/25/2020
12/29/2020
12/30/2020
1/3/2021
1/5/2021
1/9/2021
1/11/2021
1/15/2021
1/19/2021
1/21/2021
1/23/2021
1/25/2021
1/28/2021
1/29/2021
1/31/2021
2/1/2021
2/3/2021
2/6/2021
2/9/2021
2/12/2021
2/15/2021
2/17/2021
2/20/2021
2/23/2021
2/27/2021
3/1/2021
3/3/2021
3/6/2021
3/10/2021
3/14/2021
3/18/2021
3/19/2021
3/22/2021
3/25/2021
3/28/2021
4/1/2021
4/5/2021
4/7/2021
4/8/2021
4/10/2021
4/11/2021
4/13/2021
4/14/2021
4/18/2021
4/19/2021
4/23/2021
4/26/2021
4/29/2021
5/2/2021
5/4/2021
5/6/2021
5/10/2021
5/11/2021
5/13/2021
5/17/2021
5/21/2021
5/22/2021
5/24/2021
5/27/2021
5/30/2021
5/31/2021
6/4/2021
6/5/2021
6/6/2021
6/9/2021
6/12/2021
6/16/2021
6/19/2021
6/22/2021
6/26/2021
6/28/2021
6/29/2021
7/1/2021
7/5/2021
7/7/2021
7/10/2021
7/11/2021
7/14/2021
7/16/2021
7/18/2021
7/20/2021
7/23/2021
7/25/2021
7/26/2021
7/30/2021
8/1/2021
8/3/2021
8/4/2021
8/7/2021
8/11/2021
8/12/2021
8/16/2021
8/17/2021
8/18/2021
8/21/2021
8/23/2021
8/26/2021
8/29/2021
8/30/2021
9/1/2021
9/2/2021
9/4/2021
9/5/2021
9/9/2021
9/13/2021
9/14/2021
9/15/2021
9/16/2021
9/18/2021
9/19/2021
9/21/2021
9/22/2021
9/26/2021
9/28/2021
9/30/2021
10/1/2021
10/4/2021
10/7/2021
10/10/2021
10/14/2021
10/17/2021
10/19/2021
10/23/2021
10/25/2021
10/26/2021
10/28/2021
10/31/2021
11/2/2021
11/3/2021
11/4/2021
11/6/2021
11/9/2021
11/10/2021
11/11/2021
11/14/2021
11/17/2021
11/21/2021
11/24/2021
11/28/2021
11/30/2021
12/2/2021
12/4/2021
12/8/2021
12/11/2021
12/13/2021
12/17/2021
12/20/2021
12/21/2021
12/22/2021
12/26/2021
12/27/2021
12/30/2021
1/1/2022
1/2/2022
1/4/2022
1/6/2022
1/9/2022
1/10/2022
1/14/2022
1/16/2022
1/17/2022
1/18/2022
1/20/2022
1/21/2022
1/23/2022
1/25/2022
1/26/2022
1/30/2022
2/1/2022
2/4/2022
2/8/2022
2/11/2022
2/12/2022
2/16/2022
2/20/2022
2/22/2022
2/25/2022
2/27/2022
3/2/2022
3/4/2022
3/7/2022
3/8/2022
3/12/2022
3/15/2022
3/16/2022
3/20/2022
3/22/2022
3/23/2022
3/25/2022
3/28/2022
3/29/2022
3/31/2022
4/3/2022
4/4/2022
4/5/2022
4/9/2022
4/13/2022
4/16/2022
4/17/2022
4/18/2022
4/19/2022
4/22/2022
4/25/2022
4/29/2022
4/30/2022
5/2/2022
5/4/2022
5/5/2022
5/8/2022
5/11/2022
5/14/2022
5/16/2022
5/20/2022
5/24/2022
5/27/2022
5/28/2022
5/31/2022
6/4/2022
6/7/2022
6/9/2022
6/11/2022
6/13/2022
6/14/2022
6/15/2022
6/16/2022
6/17/2022
6/19/2022
6/23/2022
6/25/2022
6/27/2022
7/1/2022
7/4/2022
7/5/2022
7/6/2022
7/9/2022
7/13/2022
7/15/2022
7/16/2022
7/19/2022
7/21/2022
7/23/2022
7/27/2022
7/29/2022
7/30/2022
7/31/2022
8/1/2022
8/3/2022
8/5/2022
8/9/2022
8/13/2022
8/15/2022
8/18/2022
8/21/2022
8/24/2022
8/25/2022
8/26/2022
8/30/2022
9/3/2022
9/5/2022
9/9/2022
9/13/2022
9/14/2022
9/18/2022
9/19/2022
9/23/2022
9/24/2022
9/26/2022
9/30/2022
10/4/2022
10/5/2022
10/7/2022
10/11/2022
10/12/2022
10/13/2022
10/14/2022
10/16/2022
10/18/2022
10/22/2022
10/25/2022
10/29/2022
10/31/2022
11/2/2022
11/3/2022
11/6/2022
11/9/2022
11/10/2022
11/13/2022
11/17/2022
11/18/2022
11/21/2022
11/25/2022
11/29/2022
11/30/2022
12/2/2022
12/3/2022
12/5/2022
12/6/2022
12/7/2022
12/8/2022
12/10/2022
12/14/2022
12/15/2022
12/16/2022
12/20/2022
12/21/2022
12/23/2022
12/24/2022
12/27/2022
12/28/2022
12/30/2022
1/2/2023
1/3/2023
1/4/2023
1/8/2023
1/10/2023
1/12/2023
1/13/2023
1/16/2023
1/18/2023
1/20/2023
1/23/2023
1/27/2023
1/30/2023
2/3/2023
2/7/2023
2/9/2023
2/13/2023
2/15/2023
2/19/2023
2/20/2023
2/23/2023
2/27/2023
3/1/2023
3/5/2023
3/9/2023
3/11/2023
3/15/2023
3/17/2023
3/20/2023
3/22/2023
3/23/2023
3/25/2023
3/29/2023
3/31/2023
4/4/2023
4/5/2023
4/6/2023
4/7/2023
4/9/2023
4/12/2023
4/13/2023
4/14/2023
4/18/2023
4/20/2023
4/23/2023
4/26/2023
4/28/2023
5/2/2023
5/5/2023
5/6/2023
5/9/2023
5/12/2023
5/16/2023
5/17/2023
5/19/2023
5/21/2023
5/24/2023
5/25/2023
5/29/2023
6/2/2023
6/3/2023
6/7/2023
6/10/2023
6/12/2023
6/14/2023
6/18/2023
6/22/2023
6/26/2023
6/28/2023
6/30/2023
7/2/2023
7/3/2023

1 ACCEPTED SOLUTION

Try this:

Measure Count = 
  VAR __Table = ADDCOLUMNS('Events',"__YearMonth",YEAR([Event Date])*100+MONTH([Event Date]))
  VAR __Table1 = GROUPBY(__Table,[__YearMonth],"__Count",COUNTX(CURRENTGROUP(),[Event Date]))
  VAR __Max = MAXX(__Table1,[__Count])
RETURN
  __Max

---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

9 REPLIES 9
Greg_Deckler
Super User IV
Super User IV

@steve_geist My pleasure. "better DAX'ing direction" is definitely subjective. I can't say that my views on things like variables, CALCULATE, time "intelligence" functions and the like aren't probaby controversial. They most certainly are. It's just how I write my DAX that allows me to solve issues quickly, efficiently and consistently. So, I make no claims of best practice here, it's just what works for me.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




steve_geist
Frequent Visitor

Thanks @Greg_Deckler , tried that DAX against the samples data above and it yielded 202k as the result...not sure if I enetred code wrong?  Putting the data into a matrix visual shows the correct results is 18...  ANy suggestions?  Thanks again for insights!

_temp =
VAR __Table =
ADDCOLUMNS(
TEMP,
"__YearMonth",
YEAR( TEMP[Event] ) * 100 + MONTH( TEMP[Event] )
)
 
VAR __Table1 =
SUMMARIZE(
__Table , [__YearMonth] , "__Count" , COUNTROWS( __Table )
)
 
VAR __Max =
MAXX( __Table1 , [__Count] )
 
RETURN
MAXX(
FILTER( __Table1 , [__Count] = __Max) ,
[__YearMonth]
)

@steve_geist In the format pane, change the Data Lable Display Units to None. You will get 202307.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Greg_Deckler
Super User IV
Super User IV

@steve_geist - Perhaps try something like this:

Measure = 
  VAR __Table = ADDCOLUMNS('Table',"__YearMonth",YEAR([Date])*100+MONTH([Date]))
  VAR __Table1 = SUMMARIZE(__Table,[__YearMonth],"__Count",COUNTROWS(__Table))
  VAR __Max = MAXX(__Table1,[__Count])
RETURN
  MAXX(FILTER(__Table1,[__Count]=__Max),[__YearMonth])

---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thanks (again) @Greg_Deckler , but that DAX still yields a value of 202,307 instead of 18 which is the known correct value from the matrix visual.

Try this:

Measure Count = 
  VAR __Table = ADDCOLUMNS('Events',"__YearMonth",YEAR([Event Date])*100+MONTH([Event Date]))
  VAR __Table1 = GROUPBY(__Table,[__YearMonth],"__Count",COUNTX(CURRENTGROUP(),[Event Date]))
  VAR __Max = MAXX(__Table1,[__Count])
RETURN
  __Max

---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Thanks @Greg_Deckler , that works perfectly!

 

May I ask if my understanding of how the measure works is correct?

  • __Table adds creates a VAR duplicate table to 'Events' and adds a column to the table deriving numeric values from [Event Date] based on the Year and Month of each Event Date
    • ...why does the year need to be lultiplied by 100?
  • __Table1 creates a VAR table with the results by the months of [Event Date] grouped by the dervied numeric values and creates a column of the counts of events in each group
  • __Max returns the max of that column

 

...I think i am misunderstanding a few points though...

  • why does __Max require the MAXX functions and not simply MAX?
  • Why do numeric values of [Event Date] required since only COUNTX is used and not SUM ro anything that actually uses the derived values in an arithmetic calculation?

Sorry if questions aren;t clear, and no worries if you dont have time to answer, was just trying t understand how/why it works but regardless...IT WORKS!!!!  Thanks you!

@steve_geist First, there are 50 ways to do everything in DAX. So, basically I wanted to make sure that I arrived at a unique year and month for grouping purposes. One way of doing this is to take the year * 100 and add the month so that you get things like:

 

202001

202002

202003

202101

etc.

 

Easy way to get a "unique" key basically. Now, you could potentially attempt to use CALCULATE and MAX and combine the two table variables within the CALCULATE statement. Might work. However, that's not how I tend to write my DAX at least initially. I find it far easier to write the DAX initially and troubleshoot using table variables and such. I wrote an entire article/rant about it. CALCUHATE - Why I Don't Use DAX's CALCULATE Functi... - Microsoft Power BI Community. So, when you use table variables, you can't use CALCULATE so you have to use the iterator functions (MAXX, SUMXX, etc.)


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@Greg_Deckler , THANKS!  I just went down the rabbit hole reading your articles!  I myself am in this phase: "[Once you start to use variables]" you tend to graduate to using table variables.  I've been creating a lot of VARiables to simplify my CALCULATE operations but am definitely still running into all the issues you describe...looks like I need get more comfortable with virtual tables and the X, SUMMARIZE, and GROUPBY functions. 

Long story short, thanks for helping with the DAX for the measure I needed, but REALLY thanks for the nudge in a better DAX'ing direction 🙂

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors