cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Measure to show days in the month automatically

How can I make a column or measure that will automatically go to the calendar of 2016 and input the number of days in the month. So basically for every row in January I want it to have 31 days for 2016; February I want 29 days for 2016, etc.? 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Measure to show days in the month automatically

@AndreasA

 

I assume you have a table as below and another calendar table.

Measure to show days in the month automatically_1.jpg

 

You can create a column in Calendar table and a column in Table1 with following formula to get the result.

YearMonth = YEAR( 'Calendar'[Date] ) * 100 + MONTH( 'Calendar'[Date] )

Measure to show days in the month automatically_3.jpg

 

MonthDays = 
VAR LastDayThisMonth =
    CALCULATE (
        MAX ( 'Calendar'[Date] ),
        ALLEXCEPT ( 'Calendar', 'Calendar'[YearMonth] )
    )
RETURN
( DAY ( LastDayThisMonth ) )

Measure to show days in the month automatically_2.jpg

 

Best Regards,

Herbert

 

View solution in original post

6 REPLIES 6
Highlighted
Solution Sage
Solution Sage

Re: Measure to show days in the month automatically

So you already have a table with one row for each day, and you want a column to show how many days are in each row's month? The row for 1 Jan 2016 would have a Days In Month column that shows 31, and the row for 2 Jan 2016 would show the same, etc.

 

If that's the case, there are a few places you could do this. Done as a new column in the data modeling pane, it would be helpful if you have a Start Of Month column or some way to uniquely identify the months. With that in place, the new column would be

 

Days In Month = CALCULATE(COUNT('DateTable'[DateColumn]), FILTER('DateTable', 'DateTable[StartOfMonth]=EARLIER('DateTable'[StartOfMonth])))

Highlighted
Super User I
Super User I

Re: Measure to show days in the month automatically

@AndreasA

 

The simplest way is to use the measure or column in the calendar table as

 

DaysInTheMonth = Day(LASTDATE('Calendar'[DateKey]))

 

So each row it will add the the number of days in that year and month.

 

Try it out.

 

If this works please accept this as a Solution and also give KUDOS.

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Highlighted
Skilled Sharer
Skilled Sharer

Re: Measure to show days in the month automatically

@AndreasA - I use this calculated column:

 

Total days = DAY(DATE(DateKey[Year],DateKey[Month number]+1,1)-1)

 

Giles

Highlighted
Microsoft
Microsoft

Re: Measure to show days in the month automatically

@AndreasA

 

I assume you have a table as below and another calendar table.

Measure to show days in the month automatically_1.jpg

 

You can create a column in Calendar table and a column in Table1 with following formula to get the result.

YearMonth = YEAR( 'Calendar'[Date] ) * 100 + MONTH( 'Calendar'[Date] )

Measure to show days in the month automatically_3.jpg

 

MonthDays = 
VAR LastDayThisMonth =
    CALCULATE (
        MAX ( 'Calendar'[Date] ),
        ALLEXCEPT ( 'Calendar', 'Calendar'[YearMonth] )
    )
RETURN
( DAY ( LastDayThisMonth ) )

Measure to show days in the month automatically_2.jpg

 

Best Regards,

Herbert

 

View solution in original post

Highlighted
Advocate I
Advocate I

Re: Measure to show days in the month automatically

This worked perfectly for me! Simple. Thanks. 

Highlighted
Helper I
Helper I

Re: Measure to show days in the month automatically

Hi. I tried doing the same thing but all I'm getting is each month has 31 days. How do I fix it? 

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors