Showing results for 
Search instead 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

Months_Available Hours.jpg

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

Calendario techs.jpg

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
No Presence and Available Hours.jpg
The ERP file that rules all the report has all the hours recorded and categorized like the first column "Codigos Horas"
Hours Codes.jpg
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



Super User
Super User

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?


Miguel Félix

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português

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.




2022019012454HORA CAMPO8
2022019012554HORA CAMPO8
8102019012955HORA TALLER8
8102019013055HORA TALLER8
8812019020655HORA TALLER8

This is the JLE_JSB Table

And this is the JEG Table where the Technicians are recorded

755Javier Cobas
810Joaquin Cerecedo
881David Caballero
214Aida Rodriguez
202Juan 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




Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

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

Community News

Fabric Community News unified experience

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

Dashboard in a day with date

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!

Power BI Fabric Summit Carousel

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