Divide amount by months of current year, otherwise by all months of previous years

[Anonymous]

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!

[Anonymous · Accepted 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

[Anonymous]

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:

[Anonymous]

Hey @Anonymous,

I see the error in my ways... glad you figured it out!