Previous 12 Month Period Check for every line

[Anonymous]

Dear community, I've been struggling with a problem and I can't seem to wrap my head around it, hopefully someone from the community can help me out! 

I have a dataset containing CUSTOMER_ID, a yearmonth combination as PERIOD and a boolean CHECK_MARK that contains whether or not a customer passed a certain check during that period. My dataset looks like this (note that this is a single customer example, there are multiple customers spread over a larger range of PERIODs in the actual dataset):

PERIOD	CUSTOMER_ID	CHECK_MARK
201801	123456	1
201802	123456	0
201803	123456	0
201804	123456	0
201805	123456	0
201806	123456	0
201807	123456	0
201808	123456	0
201809	123456	0
201810	123456	0
201811	123456	0
201812	123456	0
201901	123456	0
201902	123456	0
201903	123456	0
201904	123456	1
201905	123456	0
201906	123456	0

What I am trying to achieve is to evaluate for every customer on every line if in the previous period of 12 months a checkpoint has been passed. In order to help my achieve this result I have added a RANK as Calculated Column using the following DAX formula:

RANK = RANKX(TABLE;TABLE[PERIOD];;ASC;Dense)

The results should look like this:

PERIOD	CUSTOMER_ID	CHECK_MARK	RANK	NEW
201801	123456	1	1	1
201802	123456	0	2	1
201803	123456	0	3	1
201804	123456	0	4	1
201805	123456	0	5	1
201806	123456	0	6	1
201807	123456	0	7	1
201808	123456	0	8	1
201809	123456	0	9	1
201810	123456	0	10	1
201811	123456	0	11	1
201812	123456	0	12	1
201901	123456	0	13	1
201902	123456	0	14	0
201903	123456	0	15	0
201904	123456	1	16	1
201905	123456	0	17	1
201906	123456	0	18	1

So far I've been trying the following formula's but to no avail:

NEW = 
CALCULATE(
    MAX(TABLE[CHECK_MARK]);
    FILTER(
      ALLEXCEPT(TABLE;TABLE[CUSTOMER_ID]);
        TABLE[RANK] <= EARLIER(TABLE[RANK])))

NEW = 
CALCULATE(
    MAX(TABLE[CHECK_MARK]);
    FILTER(
        ALLEXCEPT(TABLE;TABLE[CUSTOMER_ID]);
        TABLE[PERIOD] >= TABLE[PERIOD] - 100))

Does anyone have experience with a problem like this? Or can anyone spot where I'm going wrong in my approach?

Thanks a lot in advance!

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

For the example and ‘Rank’ column you have created, you can create a calculated column called ‘New’ as follows.

New =

var r = 'TABLE'[RANK]

return if([CHECK_MARK]=1,1,IF(CALCULATE(COUNTROWS('TABLE'),FILTER('TABLE',[CUSTOMER_ID]=EARLIER('TABLE'[CUSTOMER_ID])&&[RANK]>=r-12&&[RANK]<=r&&[CHECK_MARK]=1))>0,1,0))

Result:

If I misunderstand the your thoughts, please inform me of your expected output. I am glad to solve the problem for you.

Best Regards,

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you so much, Allan!

That did the trick 😀!