Number of days in month

[Anonymous]

Hello,

I need help with counting the days in each month. 

The report I'm working on is using data from a monthly point of view so i've created the calendar table by doing this:

MonthCalendar Dynamic = 
var FullCalendar = 
    ADDCOLUMNS(
        CALENDAR( DATE(LEFT(MIN(BO_FACTORY_BENCHMARK[MonthYearNum]),4),1,1), DATE(YEAR(TODAY()),12,31) ), 
        "Month Number", MONTH([Date]),
        "Year", YEAR([Date]),
        "Year-Month", LEFT(FORMAT([Date],"yyyyMMdd"),6),
        "Month Name", FORMAT([Date],"MMM"),
        "Year-MonthName", FORMAT([Date],"MMM" & " " & YEAR([Date])),
        "No of calendar days", 
    )

RETURN 
    SUMMARIZE(FullCalendar,[Month Number],[Year],[Year-Month],[Month Name],[Year-MonthName],[No of calendar days])

And for a formula i need to count the number days in each month (you can see the "No of calendar days"), is there a expression to do this?
My idea was to use the COUNTROWS on the datetable i create using calendar, but i don't know how to address it. 

Any advice is welcome 🙂

[mahoneypat]

Based on your original expression, I thought you wanted a table with a single row for each month with the # of days.  Is that not the case?

Pat

[mahoneypat · Accepted solution]

I also didn't see the expected behavior to reference the [Date] column from the CALENDAR function.  In any case, here is an example of an approach that will work with GROUPBY.

DateSummarize =
VAR datetable =
    ADDCOLUMNS (
        CALENDAR ( DATE ( 2021, 1, 1 ), DATE ( 2021, 12, 31 ) ),
        "MonthName", FORMAT ( [Date], "MMM" )
    )
RETURN
    GROUPBY ( datetable, [MonthName], "No of Days", COUNTX ( CURRENTGROUP (), 1 ) )

Pat

[Anonymous]

@mahoneypat 
Hmm okay. I don't really understand. Is this a completely new table? which I would create a relationship to and get it via RELATED?
Is it possible to get it in a calculated column?