Consecutive monthly instances of a value

[helen_brunyee95]

Hi everyone,

I have been wracking my brains and pretty much tried every option out there on forumns etc. for this, but I'm not getting anywhere fast - so any help will me HUGELY appreciated!

Essentially, I have been tasked with getting a list of Sites ("C_L_code" ) which have, at any point in the last year, had 3 consecutive months (or more) of no visits. 

Here is my table. It shows the list of sites, for every month, with total visits:

I have added a VisitsFlag (in PowerQuery) which is simply just:

each if [Visits] = null then 0 else 1)

I also have a MonthIndex column, which I added at one point during one of mym any failed attempts at a solution, but left it in, in case it's useful:

MonthIndex =

RANKX(

    FILTER(D_AllSitesMonth, D_AllSitesMonth[C_L_code] = EARLIER(D_AllSitesMonth[C_L_code])),

    D_AllSitesMonth[MonthStart],

    ,

    ASC,

    DENSE

)

Any ideas how I would achieve this? Basically need a way of knowing if VisitedFlag for a site is 0 for 3 months or more, in a row, at any point.

I'm at a loss as to where to go with it now...maybe I'm just missing something obvious?

Thanks in advance!

[harnoorsekhon · Accepted solution]

Hi @helen_brunyee95 
You create a calculated column as follows:

ConsecNoVisitStreak =
VAR CurrentSite = [C_L_code]
VAR CurrentMonth = [MonthIndex]
VAR Prev2 = CALCULATE(
    MIN(D_AllSitesMonth[VisitsFlag]),
    FILTER(
        D_AllSitesMonth,
        D_AllSitesMonth[C_L_code]=CurrentSite &&
        D_AllSitesMonth[MonthIndex]=CurrentMonth-1
    )
)
VAR Prev3 = CALCULATE(
    MIN(D_AllSitesMonth[VisitsFlag]),
    FILTER(
        D_AllSitesMonth,
        D_AllSitesMonth[C_L_code]=CurrentSite &&
        D_AllSitesMonth[MonthIndex]=CurrentMonth-2
    )
)
RETURN IF(
    [VisitsFlag]=0 && Prev2=0 && Prev3=0, 1, 0
)

You can then filter the table for rows where ConsecNoVisitStreak=1 and retrieve those C_L_code

Hope this solution helps you make the most of Power BI! If it did, click 'Mark as Solution' to help others find the right answers.
Show some love with kudos 👍 as your support keeps our community thriving!
 

[helen_brunyee95]

Hi @harnoorsekhon !

Thank you so much for getting back to me (so quickly!) and for this fab solution! This will work perfectly. I cannot tell you how much of a headache you have saved me - this is genius!! Really appreciate it 🙂

Thanks again!!