Operate constants in a table with time-dependent measures. Power Pivot.

[Syndicate_Admin]

Hello community.

I am working on estimating production losses due to downtime at the various stages of planning and operation. In the plant we have 11 machines that produce different products at different standard hourly rates, whose values we have determined experimentally with time studies, these measures are constant over time. On the other hand, in my data model I have been able to determine for each day the different times lost in the different stages of interest.

In an excel sheet I then have a table with two columns, the name of the machine and its respective production standard and on the other hand I have developed with DAX functions different measures of lost time, which depend on time. The issue is that when I try to multiply these two values to get the kilos of lost material with the following structure:

lost kilos = sum(Prod_Corregida[Shift without Programming])*sum(Prod_Ideal[EPH])

Prod_Corregida is a table with the different lost times sorted by machine and date, while Prod_Ideal is the two-column table mentioned previously with machines and the time production standard (EPH).

Relationships between tables only allow me to filter by machines and not by dates, where it throws me erroneous values. My question is then: How can I make Power Pivot recognize the EPH measure valid for any period of time on their respective machine when I use it in operations with other data that does depend on time besides the machines?.

Thanks in advance.

[Anonymous · Accepted solution]

Hi @Syndicate_Admin 

Just add a filter like Prod_Corregida[Date] = MAX(Prod_Ideal[Date]) in your measure. I build a sample.

Prod_Corregida:

Prod_Ideal:

Build a relationship between two tables by Machine.

If we use this measure we will get wrong result, due to the filter of relationship is only Machine without Date.

lost kilos = sum(Prod_Corregida[Shift without Programming])*sum(Prod_Ideal[EPH])

 Try this measure:

Measure = 
VAR _Shift = SUM(Prod_Corregida[Shift without Programming])
VAR _EPH = CALCULATE(sum(Prod_Ideal[EPH]),FILTER(Prod_Ideal,Prod_Ideal[Date] = MAX(Prod_Corregida[Date])))
RETURN
_Shift * _EPH

Measure 2 = SUMX(Prod_Corregida,[Measure])

Result is as below.

If this reply still couldn't help you solve your problem, please share a sample with me by your Onedrive for Business.

Best Regards,

Rico Zhou

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