Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Using relative date in new column

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!

Need Help
  • DataInsights's avatar
    DataInsights
    5 years ago

    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

     

4 Replies

  • DataInsights's avatar
    DataInsights
    Super User
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        DataInsights
        Super User
        5 years ago

        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