Skip to main content
cancel
Showing results for 
Search instead 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

Reply
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):

PERIODCUSTOMER_IDCHECK_MARK
2018011234561
2018021234560
2018031234560
2018041234560
2018051234560
2018061234560
2018071234560
2018081234560
2018091234560
2018101234560
2018111234560
2018121234560
2019011234560
2019021234560
2019031234560
2019041234561
2019051234560
2019061234560

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:

PERIODCUSTOMER_IDCHECK_MARKRANKNEW
201801123456111
201802123456021
201803123456031
201804123456041
201805123456051
201806123456061
201807123456071
201808123456081
201809123456091
2018101234560101
2018111234560111
2018121234560121
2019011234560131
2019021234560140
2019031234560150
2019041234561161
2019051234560171
2019061234560181

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!

1 ACCEPTED SOLUTION
v-alq-msft
Community Support
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:

xc.png

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.

View solution in original post

2 REPLIES 2
v-alq-msft
Community Support
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:

xc.png

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

Thank you so much, Allan!

That did the trick 😀!

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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