Count number of days im month and cumulative count of days by month for each year

[gvlado]

Hello !
Need help to find solution for cumulative number of days mont by month over the years.
For each year I am looking for days per month - will use it in other calculations
For each month in the year I am looking for cumulative number of days by month (sum month days)
How to get such a columns - see printscreen

Thanks a lot

[gvlado · Accepted solution]

I think I found solution and want to share with you 

------------
Days per month= CALCULATE (

    COUNT ('Calendar'[Date] ),

    FILTER (

        ALL ( 'Calendar' ),

        'Calendar'[Year]= MAX ( 'Calendar'[Year] )

            && 'Calendar'[Monthnumber] = MAX ( 'Calendar'[Monthnumber])

            && 'Calendar'[Date] <= TODAY ()

    ))

---------------
Sum month days = DATEDIFF(
STARTOFYEAR('Calendar'[Date]),
ENDOFMONTH( 'Calendar'[Date]),DAY)

It works for me

[ThxAlot]

Simple enough

YTD Days = EOMONTH(DATE([Yr],[Mth],1),0) - DATE([Yr],1,1) + 1

[gvlado]

It gives me only one date day back ???
What I don't understand
Here it is what I tried

YTD Days = EOMONTH(DATE('Calendar'[Year],'Calendar'[Monthnumber],1),0)-DATE('Calendar'[Year],1,1)+1

[bhanu_gautam]

@gvlado , For this first make sure you have one date table if not then create using below DAX

DateTable =
ADDCOLUMNS (
CALENDAR (DATE(2010, 1, 1), DATE(2025, 12, 31)),
"Year", YEAR([Date]),
"Month", MONTH([Date]),
"MonthName", FORMAT([Date], "MMMM"),
"Day", DAY([Date]),
"MonthYear", FORMAT([Date], "MMM YYYY")
)

Then create a new column in Date Table for number of days in each month

DaysInMonth =
DAY ( EOMONTH ( DateTable[Date], 0 ) )

Create a new column in the Date table to calculate the cumulative number of days by month:

CumulativeDays =
VAR CurrentYear = DateTable[Year]

Go to the Report view.
Click on the Table visualization icon to create a new table visualization.
Drag the following fields from the Date table to the Values area of the table visualization:
Year
MonthName
DaysInMonth
CumulativeDays
VAR CurrentMonth = DateTable[Month]
RETURN
CALCULATE (
SUM ( DateTable[DaysInMonth] ),
FILTER (
DateTable,
DateTable[Year] = CurrentYear &&
DateTable[Month] <= CurrentMonth
)
)

[gvlado]

What I did wrong?

Created column and it is 0K
DaysInMonth = DAY(EOMONTH('Calendar'[Date],0))
but
Created column is not 0K

CumulativeDays =

VAR CurrentYear='Calendar'[Year]

VAR CurrentMonth = 'Calendar'[Monthnumber]

RETURN

CALCULATE (

SUM ( 'Calendar'[DaysInMonth] ),

FILTER (

'Calendar',

'Calendar'[Year] = CurrentYear &&

'Calendar'[Monthnumber] <= CurrentMonth

)

)

[bhanu_gautam]

@gvlado , The issue was due to Granuality we need cummulative days at month level, I have created new summarized table for this attaching PBIX file with complete solution