cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Calculating value differences for each week

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):

Capture.PNG

 

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.

 

  1. New Pass
    Number of total machines for each week that passed, but failed previous week.
  2. New Fail
    Number of total machines for each week that failed, but passed previous week.
  3. 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:

 

Resutl.png

 

As mentioned at the beginning of the post, my source CSV is updated with new data each week, so as time goes on I will have more [WW] columns added in my PowerBI matrix. Given this I don't quite know how I can calculate the above values dynamically without hardcoding anything. Is this possible?

 

3 REPLIES 3
mahoneypat
Microsoft
Microsoft

Here is one way to do this one to get the result below.

mahoneypat_0-1601663708183.png

First you need to add a column in query or with a DAX column to get the weeknumber as an integer.  You can then use these measure expressions (they differ only in the Return part).  The IF in the Return of the New Pass measure is to prevent a result of 2 showing in WW35.

 

New Pass =
VAR thisweek =
    MAX ( Availability[WeekNumber] )
VAR summary =
    ADDCOLUMNS (
        VALUES ( Availability[Machine] ),
        "@ThisWeek",
            CALCULATE (
                COUNT ( Availability[Machine] ),
                Availability[Availability] > 0.9,
                Availability[WeekNumber] = thisweek
            ) + 0,
        "@LastWeek",
            CALCULATE (
                COUNT ( Availability[Machine] ),
                Availability[Availability] > 0.9,
                ALL (
                    Availability[WeekNumber],
                    Availability[WorkWeek]
                ),
                Availability[WeekNumber] = thisweek - 1
            ) + 0
    )
RETURN
    IF (
        thisweek
            = CALCULATE (
                MIN ( Availability[WeekNumber] ),
                ALL ( Availability )
            ),
        BLANK (),
        COUNTROWS (
            FILTER (
                summary,
                [@ThisWeek] - [@LastWeek] = 1
            )
        )
    )

New Fail =
VAR thisweek =
    MAX ( Availability[WeekNumber] )
VAR summary =
    ADDCOLUMNS (
        VALUES ( Availability[Machine] ),
        "@ThisWeek",
            CALCULATE (
                COUNT ( Availability[Machine] ),
                Availability[Availability] > 0.9,
                Availability[WeekNumber] = thisweek
            ) + 0,
        "@LastWeek",
            CALCULATE (
                COUNT ( Availability[Machine] ),
                Availability[Availability] > 0.9,
                ALL (
                    Availability[WeekNumber],
                    Availability[WorkWeek]
                ),
                Availability[WeekNumber] = thisweek - 1
            ) + 0
    )
RETURN
    COUNTROWS (
        FILTER (
            summary,
            [@ThisWeek] - [@LastWeek] = -1
        )
    )

Steady =
VAR thisweek =
    MAX ( Availability[WeekNumber] )
VAR summary =
    ADDCOLUMNS (
        VALUES ( Availability[Machine] ),
        "@ThisWeek",
            CALCULATE (
                COUNT ( Availability[Machine] ),
                Availability[Availability] > 0.9,
                Availability[WeekNumber] = thisweek
            ) + 0,
        "@LastWeek",
            CALCULATE (
                COUNT ( Availability[Machine] ),
                Availability[Availability] > 0.9,
                ALL (
                    Availability[WeekNumber],
                    Availability[WorkWeek]
                ),
                Availability[WeekNumber] = thisweek - 1
            ) + 0
    )
RETURN
    COUNTROWS (
        FILTER (
            summary,
            [@ThisWeek] - [@LastWeek] = 0
                && [@ThisWeek] = 1
        )
    )

 

Regards,

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Anonymous
Not applicable

Wow that's pretty complicated. But how do you set a measure as 'Rows' in your Matrix? Power BI won't let me do that.

dedelman_clng
Community Champion
Community Champion

Hi @Anonymous - this might be better off doing a PowerQuery/M creation of columns showing this week's Pass/Fail and Last Week's Pass/Fail. May want to post in that forum.

 

@ImkeF 
@vanessafvg  
@tex628  
@mahoneypat 
@edhans 

 

David

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors