cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Was this risk in this status last month?

I have a table let's say "Risk status table" with the Risk ID, Status, Start of month, Risk ID and Status and what I want to work out New or same status

 

Risk IDStatusStart of monthRisk ID and Status

New or same status

R11S101/01/2020R11 S1New Status
R12S101/01/2020R12 S1New Status
R11S101/02/2020R11 S1Same Status
R12S201/02/2020R12 S2New Status
R13S101/02/2020R13 S1New Status
R11S201/03/2020R11 S2New Status
R12S301/03/2020R12 S3New Status
R13S101/03/2020R13 S1Same Status

R14

S101/03/2020R14 S1New Status

 

Above is an example of data, and what I'd expect the calculated column to be. So the calculated column in Jan is all New status since there is no data before that. In Feb, the Risk ID R11 has a status of S1 which it was also in the previous month, so the calculated column is Same Status, and in Feb R12 has changed to status S2 which it wasn't the previous month so the calculated column is New Status.

 

The logic I want to use in the new calculated colum (New or same status) is to see if the string in the "Risk ID and Status" column appears in the previous month, if the string does appear in the previous month then it's the Same Stauts, if it does not then its a New Status

1 ACCEPTED SOLUTION

@Tia-H - This worked, updated PBIX attached.

Column = 
    VAR __PreviousDate = MAXX(FILTER('Table (25)',[Risk ID]=EARLIER([Risk ID]) && [Start of month]<EARLIER([Start of month])),[Start of month])
    VAR __Previous = MAXX(FILTER('Table (25)',[Risk ID]=EARLIER([Risk ID]) && [Start of month]=__PreviousDate),[Status])
    VAR __Count = COUNTX(FILTER('Table (25)',[Risk ID]=EARLIER([Risk ID]) && [Start of month]<EARLIER([Start of month])),[Risk ID])
RETURN
    IF(__Count<0 || [Status]=__Previous,"Same Status","New Status")

---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

3 REPLIES 3
Super User IV
Super User IV

@Tia-H - I mocked this up in the standard way, in attached PBIX Table (25) but for some reason it is not recognizing the return as blank (first occurrence). Maybe something is up with my system? Anyway, see if you get similar results for first occurrences being "Same Status".

Column = 
    VAR __PreviousDate = MAXX(FILTER('Table (25)',[Risk ID]=EARLIER([Risk ID]) && [Start of month]<EARLIER([Start of month])),[Start of month])
    VAR __Previous = MAXX(FILTER('Table (25)',[Risk ID]=EARLIER([Risk ID]) && [Start of month]=__PreviousDate),[Status])
RETURN
    IF(ISBLANK(__PreviousDate) || [Status]=__Previous,"Same Status","New Status")

---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@Tia-H - This worked, updated PBIX attached.

Column = 
    VAR __PreviousDate = MAXX(FILTER('Table (25)',[Risk ID]=EARLIER([Risk ID]) && [Start of month]<EARLIER([Start of month])),[Start of month])
    VAR __Previous = MAXX(FILTER('Table (25)',[Risk ID]=EARLIER([Risk ID]) && [Start of month]=__PreviousDate),[Status])
    VAR __Count = COUNTX(FILTER('Table (25)',[Risk ID]=EARLIER([Risk ID]) && [Start of month]<EARLIER([Start of month])),[Risk ID])
RETURN
    IF(__Count<0 || [Status]=__Previous,"Same Status","New Status")

---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Super User IV
Super User IV

@Tia-H , create a new column like


new column =
var _date = maxx(filter(table,[Risk ID] = earlier([Risk ID]) && [Start of month] <earlier([Start of month])),[Start of month])
var _st =maxx(filter(table,[Risk ID] = earlier([Risk ID]) && [Start of month] =_date),[Status])
return
if(isblank(_st), "New Status", if(_st = [Status] ,"Same Status", "New Status"))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors