Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |