Forum Discussion

hgzelaya's avatar
hgzelaya
Icon for Helper I rankHelper I
7 years ago
Solved

Help!! Previous month status vs next month

Hello everyone, 

 i´m having an issue with the following excercise.

 

Client IDStatusMonthCONDITION
250UNAVAILABLEabr.-19FALSE
250UNAVAILABLEmay.-19FALSE
250OKjun.-19TRUE
250OKjul.-19FALSE
250WAITINGago.-19FALSE
250OK completesep.-19TRUE
260OKabr.-19FALSE
260OKmay.-19FALSE
260OKjun.-19FALSE
260UNAVAILABLEjul.-19FALSE
260UNAVAILABLEago.-19FALSE
260OK receivedsep.-19TRUE
270OKabr.-19FALSE
270OKmay.-19FALSE
270PENDINGjun.-19FALSE
270OK donejul.-19TRUE
270OKago.-19FALSE
270OKsep.-19FALSE

 

I want a calculated column with a true/false statement in which works like this: 

 

if previous month status is anything that doesn´t contains the word "OK" in it AND "actual month contains the word "ok"="TRUE", other than this, "FALSE". 

 

I desire the same results shown in the table above.

 

This is the link for the data:

https://docs.google.com/spreadsheets/d/1L1rdcxBVGyr60FBRep0XAaeVOYNXX4a35uWNnjkK-Q4/edit?usp=sharing

 

 

Thanks for the help in advance!

Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    7 years ago

    Hello hgzelaya 

    If the Month column is an actual date I was able to get it to work with this.

    Condition = 
VAR ClientID = 'YourTable'[Client ID]
VAR PMStatus =
    CALCULATE (
        MAX ( 'YourTable'[Status] ),
        ALL ( 'YourTable' ),
        'YourTable'[Client ID] = ClientID,
        DATEADD ( 'YourTable'[Month], -1, MONTH )
    )
VAR CMStatus = 'YourTable'[Status]
RETURN
    NOT ISBLANK ( PMStatus )
    && NOT CONTAINSSTRING ( PMStatus, "OK" )
    && CONTAINSSTRING ( CMStatus, "OK" )

2 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Icon for Super User rankSuper User
    7 years ago

    Hello hgzelaya 

    If the Month column is an actual date I was able to get it to work with this.

    Condition = 
VAR ClientID = 'YourTable'[Client ID]
VAR PMStatus =
    CALCULATE (
        MAX ( 'YourTable'[Status] ),
        ALL ( 'YourTable' ),
        'YourTable'[Client ID] = ClientID,
        DATEADD ( 'YourTable'[Month], -1, MONTH )
    )
VAR CMStatus = 'YourTable'[Status]
RETURN
    NOT ISBLANK ( PMStatus )
    && NOT CONTAINSSTRING ( PMStatus, "OK" )
    && CONTAINSSTRING ( CMStatus, "OK" )

    • hgzelaya's avatar
      hgzelaya
      Icon for Helper I rankHelper I
      7 years ago

      i don´t actually know what you really did right there but, it actually worked! I appreciate it!