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

[steve_geist]

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

[Greg_Deckler]

@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.

[steve_geist]

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]

)

[Greg_Deckler]

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

[Greg_Deckler]

@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])

[steve_geist]

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.

[Greg_Deckler · 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

[steve_geist]

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!

[Greg_Deckler]

@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.)

[steve_geist]

@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 🙂