Identify Changes within specific period of time

[Kostas]

Hello Everyone, 

I need once more your help to generate a specific analysis. 
I got a table with an ID column, an Description column and an effective date column. 
I want to be able to identify and (and with the measure visualise) any ID's that the description changed within a period of 60 days based on the effective date column. 
Here an example below:

ID	Description	Eff Date
123	Desc1	14/01/2022
333	Desc2	20/01/2022
444	Desc3	30/01/2022
123	Desc4	31/01/2022
333	Desc5	05/02/2022
444	Desc6	30/06/2022

Now the measure will basically identify the ID: 123 and the ID 333 because there was a change in the description within 60 days. 

I need to be able to call out those ID's.

Thanks

[v-yueyunzh-msft · Accepted solution]

Hi , @Kostas 

According to your description, you want to "Identify Changes within specific period of time".

Here are the steps you can refer to :
(1)My test data is the same as yours.

(2)We can create a measure:

Measure = var _cur_id=MAX('Table'[ID])
var _description= MAX('Table'[Description])
var _max_date = CALCULATE( MAX('Table'[Eff Date]) , 'Table'[ID]=_cur_id ,ALLEXCEPT('Table','Table'[ID]))
var _pre_60days = _max_date-60
var _t=SELECTCOLUMNS( FILTER( ALLSELECTED('Table'),'Table'[ID]=_cur_id && 'Table'[Eff Date]<=_max_date && 'Table'[Eff Date]>= _pre_60days) ,"Des",[Description])
return
IF(COUNTROWS(DISTINCT(_t))>1,1,0)

(3)Then we can put it on the visual:

Then we can use the "1/0" to judge which meet your need.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[Kostas]

Hello @v-yueyunzh-msft  and thanks for your reply. 
Indeed my data were similar and the measure worked within a pivot table in which I placed my ID and the Measure. 
I will mark the answer as solution, but I just wondering if there is a way of making amends to the code so i can include it as column within my table. By doing that next to the ID column, the new column will mark as changed or not and then I could probably create a Pie which will have a distinct count of ID's and as a legend the new column that will separate them into "Changed" "Not Changed". 
I hope this makes sense. 

Thanks

[v-yueyunzh-msft]

Hi , @Kostas 

Thanks for you mark my solution, for your need , you can create two more measures like this:

Changed = COUNTROWS( FILTER(ADDCOLUMNS( VALUES('Table'[ID]) , "value" , [Measure]) , [value] =1))

Not Changed = COUNTROWS( FILTER(ADDCOLUMNS( VALUES('Table'[ID]) , "value" , [Measure]) , [value] =0))

Then you can put these measures on the visual:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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