Help with a DAX measure for comparing a value on previous week to current week

[Ben1981]

I have a table of data with a number of columns and I need to return a count based on this criteria

Count of IDs from the previous week from the one selected via a filter, where the status is “FAIL” and where the status in the current selected week is “Okay”

So, I’m essentially looking at failures from a previous week and seeing which are those failures are now okay.

I can write a measure for the first part but I can’t figure out the second bit, does this need to be a second measure? Or I even thought about creating a calculated column in my data that pulls the previous weeks status as a line on the current week.

Table:

ID – STATUS – WEEK

Thanks for any help

[Ben1981 · Accepted solution]

Thanks everyone but in the end I decided to create a pw column on the cw column within SQL which I'm far more comfortable with. This then left me with a simple DAX measure to do the last bit, not sure if it's the most elegant solution but it works!

[v-yilong-msft]

Hi, @Ben1981

Based on the information you have provided, I think you can create a Measure to use to calculate the number of IDs for the previous week.

Enter the appropriate DAX code to see the results you want.

Count of IDs = CALCULATE(
    COUNT('Table'[ID]),
    FILTER(
        'Table',
        'Table'[STATUS] = "FAIL" &&
        'Table'[WEEK] = MAX('Table'[WEEK]) - 1 &&
        'Table'[ID] = SELECTEDVALUE('Table'[ID]) &&
        CALCULATE(
            SELECTEDVALUE('Table'[STATUS]),
            FILTER(
                'Table',
                'Table'[WEEK] = MAX('Table'[WEEK])
            )
        ) = "Okay"
    )
)

If you can provide me with the relevant data that would be better, I can further analyze the solution for you.

How to Get Your Question Answered Quickly 

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MFelix]

Hi @Ben1981 ,

You can have several approaches however this depends on the model the context of the visualization.

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.