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
mjohnsen
Frequent Visitor

sum of hours divided by the total number of hours per year.

Hello,

 

I'm trying to make a measure that yields the sum of hours divided by the total number of hours per given year.

 

This measure gives me something similar to what I want (considering that the number of days in a year is approxemately 8760):

measure = sum(data[machine_hour_per_order) / 8760
 
However, it does not account for the past hours in the current year. Do you have any suggestions?
 
 Dummy data:
MachineDateMachine hour
101.01.20211000
101.02.20211000
101.01.202210
101.02.202220
201.01.2021500
201.02.2021500
201.01.20225
201.02.202210

The plot should look something like this:

mjohnsen_1-1669733110479.png

 

2 ACCEPTED SOLUTIONS
DimaMD
Solution Sage
Solution Sage

@mjohnsen Hi, Is this your desired outcome?
Screenshot_13.jpg


__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com

View solution in original post

v-zhangti
Community Support
Community Support

Hi, @mjohnsen 

 

You can try the following methods.
Column:

Year = YEAR([Date])

Measure:

Measure = 
Var _Sum=CALCULATE(SUM('Table'[Machine hour]),ALLEXCEPT('Table','Table'[Machine],'Table'[Year]))
Return
DIVIDE(_Sum,365*24)

vzhangti_0-1669777548737.png

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
mjohnsen
Frequent Visitor

Thanks! It seems like the measure does what I want. 

v-zhangti
Community Support
Community Support

Hi, @mjohnsen 

 

You can try the following methods.
Column:

Year = YEAR([Date])

Measure:

Measure = 
Var _Sum=CALCULATE(SUM('Table'[Machine hour]),ALLEXCEPT('Table','Table'[Machine],'Table'[Year]))
Return
DIVIDE(_Sum,365*24)

vzhangti_0-1669777548737.png

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

DimaMD
Solution Sage
Solution Sage

@mjohnsen Hi, Is this your desired outcome?
Screenshot_13.jpg


__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com

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.

Top Solution Authors