Using relative date in new column

[Anonymous]

Good day,

Let me first start out by saying I am still relatively new at Power BI so my apologies if I did something silly.

I have a compliance table that looks at a particular system and checks the below to see if it is compliant within our standards.  It is as follows:

= Table.AddColumn(#"Replaced Value", "Compliance Check", each if [status] = "Active" and
[alertStates] = "OK" and
[backupCompletePercentage] >= "99"
then "1" else "0")

I also have a proper date table with a RelativeWeek column which seems to be functioning correctly.  This table is marked as a Date table.  This is the column within the Calendar table:

RelativeWeek = ('Calendar'[StartOfWeek] - 'Calendar'[StartOfCurrentWeek])/7

What I need to do is make a new column that checks these three factors AND also checks to see if the Last Connected Date in the compliance table is within the last 2 weeks including today, then prints a 1 if true.  If we can make one column do all 4 checks that would be even better.

My problem is that I can't seem to make this work to save my life.  When I try to add such a column in the compliance table, it constantly says it doesn't find the Calendar table or the RelativeWeek column.  I was thinking maybe I needed to do some sort of IF statement to make this check but I can't even use the RelativeWeek field at all it seems.

What am I doing wrong?

Thanks!

[DataInsights · Accepted solution]

@Anonymous,

Try something like this. It does all four checks, and doesn't need RelativeWeek.

Calculated column:

Compliance Check =
VAR vToday =
    TODAY ()
VAR vStartDate = vToday - 14
VAR vLastConnDate = Compliance[Last Connected Date]
VAR vResult =
    IF (
        Compliance[status] = "Active"
            && Compliance[alertStates] = "OK"
            && Compliance[backupCompletePercentage] >= "99"
            && vLastConnDate >= vStartDate
            && vLastConnDate <= vToday,
        1,
        0
    )
RETURN
    vResult

[Anonymous]

@DataInsights  We are ALMOST there!  The column information you provided makes total sense and I have created the column.

However, it seems like it is always returning 0 and I believe this is because of the backupCompletePercentage.  It is currently a text field and this is the only way I can get the IF to work, but doesn't it need to be a numeric column to use the >= ?  How do I fix this?  Do I specify manually all the options (99, 99.1, 99.2 etc) or is there another way to pull all the values between 99 and 100?

Thanks again!

[DataInsights]

@Anonymous,

Yes, you need to change the data type of the column backupCompletePercentage to decimal number. Then remove the quotes surrounding 99:

Compliance Check =
VAR vToday =
    TODAY ()
VAR vStartDate = vToday - 14
VAR vLastConnDate = Compliance[Last Connected Date]
VAR vResult =
    IF (
        Compliance[status] = "Active"
            && Compliance[alertStates] = "OK"
            && Compliance[backupCompletePercentage] >= 99
            && vLastConnDate >= vStartDate
            && vLastConnDate <= vToday,
        1,
        0
    )
RETURN
    vResult

[Anonymous]

@DataInsights  You are the best!  This works perfectly 🙂