Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all,
I have a chart where I want to visualize the amount of hours per month. Because 2018 is the current year, I cannot simply divide the amount by 12. What I want is a measure that recogizes if its the current year, then divide by first month until today (in months). Otherwise, previous years must be divided by 12.
I think I need an IF formula, in combination with DATEDIFF, but I can't get it to work.
Net Hours per month = [Net Hours] / CALCULATE(DATEDIFF(FIRSTDATE(DimDate[Date]),TODAY(),MONTH)) > This formula works for the current year, but then I need an alternative for previous years.
Thank you in advance!
Solved! Go to Solution.
Hey @Anonymous,
Since you said your formula works for the current year, maybe try:
Net Hours per month = IF( YEAR(DimDate[Date]) = YEAR(TODAY()), [Net Hours] / CALCULATE(DATEDIFF(FIRSTDATE(DimDate[Date]),TODAY(),MONTH)), [Net Hours] / 12 )
Basically if the date is in this year, use your formula. Else, it's just Net Hours divided by 12.
Hope this helps,
Parker
Hey @Anonymous,
Since you said your formula works for the current year, maybe try:
Net Hours per month = IF( YEAR(DimDate[Date]) = YEAR(TODAY()), [Net Hours] / CALCULATE(DATEDIFF(FIRSTDATE(DimDate[Date]),TODAY(),MONTH)), [Net Hours] / 12 )
Basically if the date is in this year, use your formula. Else, it's just Net Hours divided by 12.
Hope this helps,
Parker
Hi @Anonymous
Many thanks for your reply, I have solved my problem with your help.
Your formula was unfortunately not fully working, but I managed to get it working with the following formula (basically in another order):
Net Hours per month = [Net Hours] / IF( YEAR(DimDate[Date]) = YEAR(TODAY()), CALCULATE(DATEDIFF(FIRSTDATE(DimDate[Date]),TODAY(),MONTH)), 12 )
This is the end result, for those with a similar question:
Hey @Anonymous,
I see the error in my ways... glad you figured it out!
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |