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?
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...
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.
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
|Used to group dates into weeks|
|Holds by week data relevant to how the machine was performing along with other data used to slice the model|
|Each week a small percentage of the machines have work done to them this table documents that activity|
|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
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|
and in the FieldData table (unfiltered just now) we would have many records -
|Table - FeildData|
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|
|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)|
A table that covers the period we have data on
|Week||Average Uptime (-2 weeks)||Average Uptime (+2 weeks)||Diff in Uptime|
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.
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
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?
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.