cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

## Compare a subset of machines performance in a week and what it was 2 weeks before

Hi,

I have been stuck on this for a couple of days now.

The scenario is like this, I have a table that is a "weekly performance" list for a population of machines and their performance with each machine having a unique ID. So the Table is Machine_ID, Week_Date, Performance.

I have also added an Index key that is the concatenation of Machine_ID & Week_Date

The second table  "serviced machines" lists what machines were serviced in a particular week. So the Table is Machine_ID, Week_Date and a concatenation to create an Index as above.

What I want to do is take all the Machine_ID's that were serviced in a week and calculate their average performance for that week AND ALSO what was there average performance 2 weeks earlier.

I have tried a number of ways but with without success -- any ideas how I could do this?

6 REPLIES 6
Super User

That being said...See my article on Mean Time Before Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Hi Greg,

Thanks for the link very useful and hopefully I have managed to take most on board with this reply.  I looked at your MTBF article it was interesting and I think maybe someway to what I want to do but i am specifically trying to use subgroups of machines and seeing the before and after some Activity was applied.

Problem Statement

I'll use a a simplified model to explain as the implementation is with a much larger model we run.

Assume there is a population of 100 machines/objects and every week as well as the normal data we get on these we also get Field data on UpTime for a subset of the base. We perform work on a small percentage of the machines every week and the "Exam Question" I want to answer is for the small subset of machines were work was perform, and we have UpTime data, did their Average UpTime improve when we compare their average UpTime 2 weeks before the action and 2 weeks after and calculate this each week for the weeks unique small pool of machines.

I have the following Tables in the Model

 Calendar Used to group dates into weeks History Holds by week data relevant to how the machine was performing along with other data used to slice the model Activity Each week a small percentage of the machines have work done to them this table documents that activity FieldData This table holds by week a subset of the machines in the History Table  where we get Uptime performance by machine.

The relationship currently flow top down in the order of the list above

 Relationships Calendar History WeeskStart 1:* WeekStart History Activity WeekStart_ID 1:* WeekStart_ID Activity FieldData WeekStart_ID 1:* WeekStart_ID

I have simplified the calculations I am looking for by looking at 1 week in this explanation but I need the solution to iterate through all the weeks.

In the week of 22/04/2018 there where 5 machines that had work done to them so if we filter this weeks ACTIVITY table for 22/01/2018 and is there a record in FieldData table we see -

 Table - ACTIVITY WEEK ID WeekStart_ID Other field used in slicers 22/04/2018 ID123 22/04/2018_ID123 … 22/04/2018 ID976 22/04/2018_ID976 … 22/04/2018 ID532 22/04/2018_ID532 … 22/04/2018 ID232 22/04/2018_ID232 … 22/04/2018 ID345 22/04/2018_ID345 …

and in the FieldData table (unfiltered just now) we would have many records -

 Table - FeildData WEEK ID WeekStart_ID Uptime … 22/04/2018 ID123 22/04/2018_ID123 0.98125 … 01/07/2018 ID999 01/07/2018_ID99 0.99658

From this data I am trying to do the following

 The calculation: Take the 5 ID's in week 22/04/2018 and create a table from FieldData that contains all the records associated with these 5 ID in the table Then Average Uptime (-2 Weeks) = CALCULATE(AVERAGE(FieldData[Uptime], DATEADD([CALENDAR[WEEK}, - 14,  DAYS) Average Uptime (+2 Weeks) = CALCULATE(AVERAGE(FieldData[Uptime], DATEADD([CALENDAR[WEEK}, + 14,  DAYS) Diff in Uptime = Average Uptime(+2 Weeks) - Average Uptime(-2 Weeks)

Expected results

A table that covers the period we have data on

 Week Average Uptime (-2 weeks) Average Uptime (+2 weeks) Diff in Uptime 01/04/2018 99.844% 99.945% 0.101% 08/04/2018 98.424% 99.546% 1.122% 15/04/2018 96.425% 98.675% 2.250% 22/04/2018 98.354% 98.987% 0.633% 29/04/2018 98.999% 99.125% 0.126% 06/05/2018 97.568% 98.832% 1.264%

Current Status

I tried a number of approaches and still stuck - it might be I am looking at this from the wrong prospective I know its possible. In writing up this I am question myself in do I have the structure correct maybe my FeildData needs to have a relationship with the History table and not the Activity Table

Any help/guidance would be gratefully appreciated.

Cheers

Employee

Hi @Timaru_Golf,

How do you calculate the [Uptime]? In your sample table, how to get 0.98125 and 0.99658?

Best Regards,
Angelia

Hi,

In the FieldData table each machine has a row per week where the uptime for that week is stored.

Regards

Andrew

Today I have been trying to break the problem into mini steps and build up to a solution..

So first how many units where touch in a week. The following measure works used in a Matrix table Week for the Rows works fine

`Units Touched = COUNTROWS(VALUES(FieldData[ID])`

Next I tried to create the same results but now opening the data up to the full table so I could walk back and forth in the data later but just now keep it this week.

```TEST ALL Filtered this week & ID's =

VAR Units_in_this_week = VALUES(FieldData[ID])

RETURN

CALCULATE(
COUNT(FieldData[ID]),
FILTER(
ALL(FieldData),
COUNTROWS(
FILTER(
FieldData,
&& FieldData[ID] in Units_in_this_week
)
)
)
)```

The Above Formula I adapded from a post by Alejandro Zuleta and link is

https://stackoverflow.com/questions/40120580/power-bi-dax-calculating-last-week-sales-for-all-the-fi...

From what I can see it is basically ignorning the "Units in Field" condition and just returning all the records that week.

So what am I doing wrong with -- && FieldData[ID] in Units_in_this_week  -- or just my basic logic?

Regards

Andrew

Employee

Hi @Timaru_Golf,

You need to list the specific value for Units_in_this_week part, please review the IN function.

Thanks,
Angelia