cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Previous 12 Month Period Check for every line

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?

1 ACCEPTED SOLUTION
Community Support

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:

Best Regards,

Allan

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

2 REPLIES 2
Community Support

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:

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
Not applicable

Thank you so much, Allan!

That did the trick 😀!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors