cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

## Trying to Add External Data available to create a Measure

Hi, I am trying to calculate the available hours from our technicians, for this we have the all kind of  Hours record in a table from our ERP ( PowerBi is directly connected to it) and outside the system added to PowerBi  the available  hours for the year in an excel table.

I tried in two ways :

1 - Add a Excel table With the month and the Calendar Available Hours for each Month

2 - Add a Excel table with the Tech Name, Month and Available days for each one

I Have managed to create a measure for each month adding manually the Available hours and substracting to this number the number of  no presence Hours with this formula

Available Hours January = SUMX(SUMMARIZE(JEG,JEG[Técnico],"Horas",168 - [NO PRESENCE]), [Horas])
But i have to create a measure this way for each month and for each Year, so if you change the date to check the data you have to change the selection in the tables to match the right hours
The ERP file that rules all the report has all the hours recorded and categorized like the first column "Codigos Horas"
And with this categorizations i created the measures that build the report
Non Billed Hours = CALCULATE(SUM(JLE_JSB[Horas Totales]),'Tipos Horas'[Tipo]="No Facturadas")
OT's Hours = CALCULATE(SUM(JLE_JSB[Horas Totales]),'Tipos Horas'[Tipo 2]= "Horas en Ots")
No Signed Hours= [Available Hours January ] - [No billed Hours] - [OT's Hours]
Any idea how to do it automatically ?
Thanks and Regards

Alejandro

2 REPLIES 2
Super User

How is the ERP file setup for the hours calculation? You present the classification but the question I posing is if there are in that file the additional data as tech, number of hours and date?

Based on the overal request you should not have the need to add the tech/available hours per month table since to what I can see everyone has the same number of hours available so the firts table that you present is sufficient to make it dynamic.

Can you share a small sample of data?

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Frequent Visitor

Hi! Sorry for my late response!

For the Hours calculation i created a calculated column that  joined  the worked  hours and the Km transformed to hours

in the Horas Totales Column because  are used to count as work hours.

Here  you have an example of the tables , etc i use, please let me know if you need more info.

 LEEEID LEDATW LELABC LEDDES Horas Totales 755 20190103 VA VACACIONES 8 214 20190128 FO FORMACION 8 202 20190124 54 HORA CAMPO 8 202 20190125 54 HORA CAMPO 8 810 20190129 55 HORA TALLER 8 810 20190130 55 HORA TALLER 8 881 20190206 55 HORA TALLER 8 881 20190118 35 KILOMETROS 7,5 810 20190108 38 KILOMETROS 3,125

This is the JLE_JSB Table

And this is the JEG Table where the Technicians are recorded

 EGGEEID Técnico 755 Javier Cobas 810 Joaquin Cerecedo 881 David Caballero 214 Aida Rodriguez 202 Juan Pereira

I Also have the Calendar tables explained in the previous post, if the first table is ok, perfect.

And here also you have  the relations between the different tables

And here you have the measures that i have created

The Idea is to substitute this measures for other that automatically calculate The available hours and the non signed hours when selecting the date in filter created for that

Many Thanks and Regards

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors