Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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
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
Proud to give back to the community!
Thank You!
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
Hi Dale,
Thanks.
Reagrds,
Arvind
Hi @arvindyadav,
Could you please mark the proper answer as solution if it's convenient for you? That will be a help to the others.
Best Regards!
Dale
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.
Hi ,
Thanks I got another formula for these i.e,
1)This formula use in to create new column
DaysinMonth = DAY(
IF(
MONTH('Query1'[Date]) = 12,
DATE(YEAR('Query1'[Date]) + 1,1,1),
DATE(YEAR('Query1'[Date]), MONTH('Query1'[Date]) + 1, 1)
) - 1
)
After above formula please make a measure
No. of Days = MAX(Query1[DaysinMonth])
Now you can getting your no of days in a months.
Where Query1 indicate your table name.
Regards,
Arvind
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
122 | |
101 | |
71 | |
61 |