Trying to Add External Data available to create a Measure

[alejandrodelgad]

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

[MFelix]

Hi  @alejandrodelgad ,

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?

[alejandrodelgad]

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