Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

 

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

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
Not applicable

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:

 

Power BI forum 03-05-2018.PNG

Anonymous
Not applicable

Hey @Anonymous,

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.