I need to find one way or another the following formula in Power BI:
Total Hours of Use of a Machine = Hours Function * Range of Functioning
where Hours Function is the hours of use of a certain machine. Take it at a cost that for each machine is a constant and Range of Functioning is the difference between the final date of the evaluation and the initial date, measured in hours.
For example, I want to measure the Total Hour Use of a Machine in between 15/10/2019 and 14/20/2019. So the math is the following:
Assume: 2 machines
Hours Function machine A: 6
Hours Function machine B: 9
Range of Functioning = 15/10/2019 - 14/10/2019 = 24 hours
The output:
Total Hours of Use of a Machine A: 144
Total Hours of Use of a Machine B: 216
I need to do that in Power BI in a way that any user moving a slicer of date, refresh the Total Hours of Use of a Machine.
I don't find any way that I can get the difference between the final date of the evaluation and the initial date and put in DAX or a new column.
You have to use measures if you want to recalculate the value when you change the date with a slicer.
The first step is to be sure to be able to calculate the number of day selected by your slicer.
It seems to be easy but if you use the function FirstDate on your calendar table directly integrated in PowerBI. You'll never have what you expect. The tricks here to get this number of day is to calculate the number of rows in your calendar table with the function countrows.
When you have this number day you just have to multiply this by 24 ( hours) and by the sum of your "Hours Function machine".( 6 for A 9 for B in your example ) ( It's important to use the sum or another aggregate function like average because if you have multiple value the measure fall in error because it need only one value to multiply).
The dax formula looks like : = COUNTROWS(('Calendar')) * Sum(Machine[Hours function])
You can then display this measure filtered by the Machine Name and a date slicer(based on your calendar table).