I have some 'availability' numbers (a percentage) for a bunch of machines on a weekly basis. My raw CSV data looks like this:
Machine,WW,Availability
A,WW35,0.9
B,WW35,0.95
C,WW35,1
D,WW35,0.87
A,WW36,1
B,WW36,1
C,WW36,0.84
D,WW36,0.94
A,WW37,0.75
B,WW37,0.98
C,WW37,0.91
D,WW37,0.89
A,WW38,1
B,WW38,0.88
C,WW38,0.99
D,WW38,0.95
Data source is updated weekly and new Work Week (WW) availability data is added for each machine. A machine is deemed 'Pass' if the availability for that week is > 90%. I calculate the 'Pass' measure as below.
Pass =
VAR varCount = CALCULATE(COUNTA(data[Availability]), data[Availability] > 0.9)
RETURN
IF(varCount = BLANK(), 0, varCount)
Pass count for each machine for each week, displayed in a matrix, looks like this (given above data):
Now, I want to calculate some figures for these pass values for each machine. My actual needs are a bit complex, but few of the most basic things I wanted calculated are shown below.
New Pass: Number of total machines for each week that passed, but failed previous week.
New Fail: Number of total machines for each week that failed, but passed previous week.
Steady: Number of total machines for each week that the condition didn't change.
To better illustrate, I put my desired results in an Excel file:
In PowerBI community forums as well as here, there are many similar questions, but in all of them the difference calculations seem to specify a constant column name, or the max/min values of the date (WW in my case) columns. But what I want is to generate the difference for each two adjacent WWs, and as my source is updated weekly, I want to show the difference for the new week as well.
Is this possible?
EDIT
I'm editing the question to provide more details after I tried @mkRabbani's answer below.
As I explained in the comments, it won't let me create the measure as you have. In DAX
, it won't let me use column names inside of IF
. But, I got around the problem by creating few measures, which would be the equivalent of Availability
and PrevAvailability
.
Pass Measure
Pass =
VAR varCount = CALCULATE(COUNTA(data[Availability]), data[Availability] > 0.9)
RETURN
IF(varCount = BLANK(), 0, varCount)
PrevPass Measure
This is the count of machines that passed previous week.
PrevPass =
VAR varCount = CALCULATE(COUNTA(data[PrevAvailability]), data[PrevAvailability] > 0.9)
RETURN
IF(varCount = BLANK(), 0, varCount)
I confirmed that these values are as expected by putting them in a matrix.
Then I created the equivalent of your measure calculating NewPass etc.
PassFailSteadyColumn =
IF(
'data'[PrevPass] = 0,
BLANK(),
IF(
'data'[PrevPass] = 0 && 'data'[Pass] = 1,
"New Pass",
IF(
'data'[PrevPass] = 1 && 'data'[Pass] = 0,
"New Fail",
"Steady"
)
)
)
So far so good, but now when I try to drag-drop the PassFailSteadyColumn
to my matrix's Rows
field, it simply won't let me do that. What gives?