Calculate diff of adjacent columns in PowerBI matrix

Question

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?

Answer 1

Perform this below Power Query steps-

Step-1: Duplicate your column WW

Step-2: Split the new column using Fixed 2 character. Your WW35 is now available in 2 different column as WW and 35.

Step-3: Create a new Custom Column = (Spllitted Second Value - 1). This will basically return the Previous Week number per row.

Step-4: Now Merge the table with same table using below relation-

column: **Machine** and **Previous Week**
column: **Machine** and **Week Number (Spllitted Second Value)**

Step-5 Expand the column and keep only column "Availability" and rename it as Availability Prev Week. You will be a final output as below-

Here is the code from Advance Editor for your reference-

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XdDBCcAgDEDRXXKW0lJTk2O1O3gQ91+jkohGT8Ij+QRLgRcc5Hxje86DoboCcSEUS9Mugc8OURDrrWcMxR3ShLblbUmNvS0FsYA2psZke92Wy9SIbY/2y0iHlpgas43R+Iv6Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Machine = _t, WW = _t, Availability = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Machine", type text}, {"WW", type text}, {"Availability", type number}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "WW", "WW - Copy"),
    #"Split Column by Position" = Table.SplitColumn(#"Duplicated Column", "WW - Copy", Splitter.SplitTextByPositions({0, 2}, false), {"WW - Copy.1", "WW - Copy.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"WW - Copy.1", type text}, {"WW - Copy.2", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"WW - Copy.2", "week number"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "prev week number", each [week number] - 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom", {"Machine", "prev week number"}, #"Added Custom", {"Machine", "week number"}, "Added Custom", JoinKind.LeftOuter),
    #"Expanded Added Custom" = Table.ExpandTableColumn(#"Merged Queries", "Added Custom", {"Availability"}, {"Added Custom.Availability"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Added Custom",{{"Machine", Order.Ascending}, {"week number", Order.Ascending}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Sorted Rows",{{"Added Custom.Availability", "Availability Prev Week"}})
in
    #"Renamed Columns1"

Now get back to report clicking Close and Apply and create these below 2 measures-

total_count = count('Table (3)'[Machine])

pass fail steady column = 

if(
    'Table (3)'[Availability Prev Week] = BLANK(),
    BLANK(),
    if(
        'Table (3)'[Availability Prev Week] <= .9 && 'Table (3)'[Availability] > .9,
        "New Pass",
        if(
            'Table (3)'[Availability Prev Week] > .9 && 'Table (3)'[Availability] <= .9,
            "New Fail",
            "Steady"
        )
    )
)

Now configure the Matrix as below-

Here is the final output-

You can remove the red marked line from the visual using filter

In my example, table name is considered Table (3). Adjust accordingly.