how to check for ID's with change in status/category from last 3 months, 6 months, 9 months etc

[pbilearning]

Hi Everyone,

I have a quick doubt please if anyone can help?

I am working on a data provided with ID's, months starting from June'19 to March'20(which will extend till June'20) and category/state(text data) of id. I want to look for ID's that have not changed their state from last 3 months, last 6 months,  last 9 months and so on. Can we do that on powerbi? 

[Greg_Deckler]

Yes, you would generally do that something like:

3 Months the Same Column =
  VAR __Current = [Category]
  VAR __Table = FILTER('Table',[Month] >= ([Month] - 90))
  VAR __Check = FILTER(__Table, [Category] <> __Current)
RETURN
  IF(ISBLANK(__Check), "Same", "Changed")

[pbilearning]

Hi @Greg_Deckler 

I tried your formula by adding another column in data, but I am getting below error on power bi. Is there anything I am doing wrong

[pbilearning]

@Greg_Deckler 

I have attached excel file and PBI for your reference.Excel File (Sample) PBI  

Really Thanks for your help! 🙂

[Greg_Deckler]

OK, sorry, try this:

3 Months the Same Column = 
  VAR __Current = [Category]
  VAR __Table = FILTER('FactTable1',[Month] >= ([Month] - 90) && [ID] = EARLIER([ID]))
  VAR __Check = FILTER(__Table, [Category] <> __Current)
RETURN
  IF(ISBLANK(COUNTROWS(__Check)), "Same", "Changed")

PBIX is attached:

[pbilearning]

Hi @Greg_Deckler 

Thanks for your quick help 🙂

It is working, but its working for account changin for all months rather than just 3 months or 6 months or 9 months.  I have attached PBI with data for other months  data included and screenshot for your reference

[Greg_Deckler · Accepted solution]

Updated and attached:

3 Months the Same Column = 
  VAR __Current = [Category]
  VAR __Table = FILTER('FactTable1',[Month] <= EARLIER([Month]) && [Month] >= (EARLIER([Month]) - 90) && [ID] = EARLIER([ID]))
  VAR __Check = FILTER(__Table, [Category] <> __Current)
RETURN
  IF(ISBLANK(COUNTROWS(__Check)), "Same", "Changed")