How to do it...

The SUMMARIZE function has the following syntax:

SUMMARIZE(<table>, <groupBy_columnName>[, <groupBy_columnName>]…[, <name>, <expression>]…)

To use the SUMMARIZE function to return the number of weekdays in each month as well as the first day when each weekday occurs in each month, create a new table with the following formula:

R05_summarizedTable = 
    SUMMARIZE(
        'R05_Table', // This is the table to summarize
        [Month], // This is the column by which we want to group values
        [Weekday], // This is a second column by which we want to group values
        "# of Days", // Create a column called "# of Days"
        COUNTROWS('R05_Table'), // Return the count of rows for "# of Days"
        "First Date", // Create a second column called "First Date"
        MINX('R05_Table','R05_Table'[Value]) // Return first date weekday occurs in the month in "First Date"
    )

An excerpt of the table returned by this formula is as follows:

Month	# of Days	Weekday	First Date
January	5	Wednesday	1/1/2020 12:00:00 AM
February	4	Wednesday	2/5/2020 12:00:00 AM
March	4	Wednesday	3/4/2020 12:00:00 AM
April	5	Wednesday	4/1/2020 12:00:00 AM
May	4	Wednesday	5/6/2020 12:00:00 AM
June	4	Wednesday	6/3/2020 12:00:00 AM
July	5	Wednesday	7/1/2020 12:00:00 AM
August	4	Wednesday	8/5/2020 12:00:00 AM
September	5	Wednesday	9/2/2020 12:00:00 AM
October	4	Wednesday	10/7/2020 12:00:00 AM
November	4	Wednesday	11/4/2020 12:00:00 AM
December	5	Wednesday	12/2/2020 12:00:00 AM
January	5	Thursday	1/2/2020 12:00:00 AM
February	4	Thursday	2/6/2020 12:00:00 AM

 

We can also use the GROUPBY function to return the same information in a table. The GROUPBY function has the following format:

GROUPBY (<table>, [<groupBy_columnName1>], [<name>, <expression>]… )

To do this, create a new table with the following formula:

R05_groupedTable = 
    GROUPBY(
        'R05_Table', // This is the table to group
        [Month], // This is the column to group by
        [Weekday], // This is a second column by which we want to group values
        "# of Days", // Create a new column in this table called "# of Days"
        COUNTX(CURRENTGROUP(),'R05_Table'[Value]), // Return the count of values for "# of Days"
        "First Date", // Create a second column called "First Date"
        MINX(CURRENTGROUP(),'R05_Table'[Value]) // Return first date weekday occurs in the month in "First Date"
    )

This formula returns a table that is identical to our SUMMARIZE formula, except that the order of the rows differs slightly.