Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi,
I have total hours of work for each month this year. I would like to create a formula to have -
Total Hours / CountDaysInMonth / 12.
So let's say I have 3000 hours in January, I'd like to have a way to calculate it - 3000 / 31 / 12.
Just having a hard time figuring out how I should have it count the days of the month. I have a Date table that is connected in the Model that has Month Name and DayInWeek.
Thanks for any help!
Solved! Go to Solution.
Hi @Scaffnull
You could add a new column to the date table to store number of days in the month.
Here's some DAX to do that. You could do it in Power Query / M if you prefer.
Days in Month =
VAR _Month = MONTH('Date'[Date])
VAR _Year = YEAR('Date'[Date])
VAR _Result =
CALCULATE(
DAY(MAX('Date'[Date])),
FILTER('Date',
MONTH('Date'[Date]) = _Month && YEAR('Date'[Date]) = _Year
)
)
RETURN
_Result
Hi @Scaffnull
You could add a new column to the date table to store number of days in the month.
Here's some DAX to do that. You could do it in Power Query / M if you prefer.
Days in Month =
VAR _Month = MONTH('Date'[Date])
VAR _Year = YEAR('Date'[Date])
VAR _Result =
CALCULATE(
DAY(MAX('Date'[Date])),
FILTER('Date',
MONTH('Date'[Date]) = _Month && YEAR('Date'[Date]) = _Year
)
)
RETURN
_Result
@PaulOlding It solved it. It didn't work on my data table, but for the date table it worked like a charm. I had a month column in my data table so I just created a new column in my data table and made it related to the "Days in Month" column. Thanks again!
User | Count |
---|---|
23 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
25 | |
12 | |
11 | |
7 | |
6 |