cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Timaru_Golf
Helper III
Helper III

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
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

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
WeeskStart1:*WeekStart
   
History Activity
WeekStart_ID1:*WeekStart_ID
   
Activity FieldData
WeekStart_ID1:*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    
WEEKIDWeekStart_IDOther field used in slicers
 22/04/2018ID123 22/04/2018_ID123 
 22/04/2018ID976 22/04/2018_ID976 
 22/04/2018ID532 22/04/2018_ID532 
 22/04/2018ID232 22/04/2018_ID232 
 22/04/2018ID345 22/04/2018_ID345 

 

 

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

 

Table - FeildData   
WEEKIDWeekStart_IDUptime
   
 22/04/2018ID123 22/04/2018_ID1230.98125
   
01/07/2018ID99901/07/2018_ID990.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

 

WeekAverage Uptime (-2 weeks)Average Uptime (+2 weeks)Diff in Uptime
01/04/201899.844%99.945%0.101%
08/04/201898.424%99.546%1.122%
15/04/201896.425%98.675%2.250%
22/04/201898.354%98.987%0.633%
29/04/201898.999%99.125%0.126%
06/05/201897.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

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

Hi @Timaru_Golf,

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

Thanks,
Angelia

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.