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

[Timaru_Golf]

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?

[Greg_Deckler]

Need sample/example data in text form. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

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...

[Timaru_Golf]

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

Answering the Exam Question

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

[v-huizhn-msft]

Hi @Timaru_Golf,

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

Best Regards,
Angelia

[Timaru_Golf]

Hi,

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

Regards

Andrew

[Timaru_Golf]

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,
                   EARLIER(FieldData[Date]) = DATEADD(Calendar[WeekStart], -0, DAY)
                   && 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

[v-huizhn-msft]

Hi @Timaru_Golf,

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

Thanks,
Angelia