cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted

@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
Highlighted
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!

Highlighted
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!




Highlighted

@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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors