Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
hgzelaya
Helper I
Helper I

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!

1 ACCEPTED SOLUTION
jdbuchanan71
Super User
Super User

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

StatusCheck.jpg

View solution in original post

2 REPLIES 2
jdbuchanan71
Super User
Super User

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

StatusCheck.jpg

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.