Forum Discussion
Count Of Days In Month.
Hi All,
I need to count no of days in given month.
i.e,
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.?
Regards,
Arvind
Hi ,
DaysInMonthColumn=SWITCH(MONTH([Date]), 1, 31, 2, 29, 3, 31, 4, 30 , 5, 31, 6, 30, 7, 31, 8, 31 , 9, 30, 10, 31, 11, 30, 12, 31 , BLANK() )The above formula is useful for me . I also want no of days in 5 months . So how can I add those number.
Regards,
Arvind
Hi arvindyadav,
Would you like to try this formula as a calculated column?
DaysInMonth = DAY ( EOMONTH ( [Date], 0 ) )
Best Regards!
Dale
9 Replies
DaysInMonthColumn=SWITCH([Month], 1, 31, 2, 29, 3, 31, 4, 30 , 5, 31, 6, 30, 7, 31, 8, 31 , 9, 30, 10, 31, 11, 30, 12, 31 , BLANK() )Hi,
I am getting blank column.
I think you need my data table for it . Please can you provide me answer by judging my data.
Please find the attached Image.
Regards,
Arvind
Maybe:
DaysInMonthColumn=SWITCH(MONTH([Date]), 1, 31, 2, 29, 3, 31, 4, 30 , 5, 31, 6, 30, 7, 31, 8, 31 , 9, 30, 10, 31, 11, 30, 12, 31 , BLANK() )?
Basically, the SWITCH statement evaluates the first parameter, in this case "MONTH([Date])" which should return the numeric value of the month for the given date for each row. 1 for January, 2 for February, etc. The rest of the parameters are in pairs. So, if January then month should return 1 and the SWITCH statement should return 31, for February, 2 and thus 29, and so on.
I took a less technical approach to this solution. I imported a list of all dates for 3 years from Excel. (Make it as long as you need.) I had excel format the date as YYYY-MM-DD before importing. ** Make sure to convert your date column back to TEXT for now.** I splt the column by the Right Most '-' delimeter. This gives me my first screen shot below.
I now Grouped the data by change in 'Post Split Date' and Counted the rows as a result. I then changed the Post Split (currently showing YYYY-MM as TEXT ) back into a date and Power BI was nice enough to assign it to the frist of each month automatically.
I now have a Month that can be queried or referenced and the Number of Days in that month for as long out / back as your original excel import. FOrrest
Hi ,
DaysInMonthColumn=SWITCH(MONTH([Date]), 1, 31, 2, 29, 3, 31, 4, 30 , 5, 31, 6, 30, 7, 31, 8, 31 , 9, 30, 10, 31, 11, 30, 12, 31 , BLANK() )The above formula is useful for me . I also want no of days in 5 months . So how can I add those number.
Regards,
Arvind
Hi arvindyadav,
Would you like to try this formula as a calculated column?
DaysInMonth = DAY ( EOMONTH ( [Date], 0 ) )
Best Regards!
Dale
