Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
nayan28apr
Helper I
Helper I

DAX to find future date of same record infront of previous date record

Hi All,

 

I get same records multiple times in my table, differentiated with time stamp column i.e. Last Modified Date.

I want to take future date of same record in a calculated column infront of previous record. Just to know when this record came next time after this date.

 

ID        Status             Last Modified date    Desired result

100      Closed            21/03/2021  

100      Resolved        12/03/2021              21/03/2021

100      Assigned        01/03/2021              12/03/2021


Thanks in advance!

1 ACCEPTED SOLUTION
ryan_mayu
Super User
Super User

@nayan28apr 

you can try to create a column

 

 

Column = minx(FILTER('Table','Table'[ID]=EARLIER('Table'[ID])&& 'Table'[Last modifty date]>EARLIER('Table'[Last modifty date])),'Table'[Last modifty date])

 

1.PNG

 





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

Proud to be a Super User!




View solution in original post

10 REPLIES 10
nayan28apr
Helper I
Helper I

@ryan_mayu Thank you so much, this worked.

I have one another question to take this solution ahead. I thought of getting future dates but seems tricky.

I want to show count of this incident id for each week in different status. But the clause here is till March 20th this incident id should be reported into active state and from 21st it should be counted in resolved state each week till the time we get next update.

ID        Status             Last Modified date    

100      Resolved        21/03/2021              

100      Active             01/03/2021              

 

this is the sample graph in which on x axis week numbers are added to plot counts of active, resolved, pending, cancelled,closed status in bar and lines respectively.

nayan28apr_0-1616351774730.png

Any suggestion, will be very helpful for us to move ahead.


Thanks In Advance,

Nayan 

@nayan28apr 

I saw your x-ais disply as month. If a case changes the status from A to B on 2021/3/21, how to count status A and B?

status A is 1 for Mar and status B is also 1 for Mar?

 





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

Proud to be a Super User!




Hi, Thanks for your reply.

These are weeks on x axis like 2021-01, 2021-02 etc. We want to show weekly graphs for the count of incident in each states.

In this below example for week 9,10,11 this incident id 100 should be counted in active state, and from week no 12 onwards in resolved state.

ID        Status            Last Modified date    

100      Resolved       21/03/2021  (week 12 onwards in resolved state)

100      Active            01/03/2021 (week 9,10,11 incident id 100 should be counted in active state)   

Thanks,

Nayan Rathi

@nayan28apr 

these two days are the first day of a week and the last day of a week

Is it possible that we change status in the middle of a week? If so ,how to count based on below data?

ID        Status            Last Modified date    

100      Resolved       18/03/2021 

100      Active            03/03/2021

 

 





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

Proud to be a Super User!




Hi, Thanks for your reply.
No, it can be any date of the week not always start date and end date.
I have pasted data below in which we need to consider incident id , status, last modified date and next record date column is calculated with your formula to show when this record will come next time in future dates.

We want to plot this as from Jan 7th till Jan 22nd it should be counted in resolved state. Similary if want to plot a weekly graph to this should be counted in W1, W2, W3 of 2021 but in W4 it should be counted as closed state and its dead now no more counting because its closed. If its another status like, active, pending, cancelled, resolved we need to keep on counting this record till the time we get another record update.


@nayan28apr 

please see the attachment below.





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

Proud to be a Super User!




Hi, Thanks for your help.
https://drive.google.com/file/d/1ULhnnUj2qdL3j9e9cTh805V1i4nbJLSx/view?usp=drivesdk

I have uploaded my file here. I find one challenge when we are in the latest week for this desired date is missing. Can you please help to check this attached final file for us?

I have one another ask, in which we need to use slicer of assigned group from the same data table. When we use calculated columns than can't use this filters with it. So thought to use measure with same calculation.  I find the same challenge here also to restrict current week.

@nayan28apr 

I think that is becuase the date is blank, so it can't be compared with datetime.

Do you want to count the date is blank or not?

 

1.PNG2.PNG

Column = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[first]<='Datetime'[Date]&&'Table'[last]>='Datetime'[Date]))

Column 2 = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[first]<='Datetime'[Date]&&'Table'[last]>='Datetime'[Date]&&'Table'[Status]="Resolved"))+ CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[first]<='Datetime'[Date]&&ISBLANK('Table'[last])&&'Table'[Status]="Resolved"))




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

Proud to be a Super User!




Incident Id    Status        Last Modified Date    Next Record Date
INC01          Closed         23/01/2021
INC01          Resolved      07/01/2021               23/01/2021

ryan_mayu
Super User
Super User

@nayan28apr 

you can try to create a column

 

 

Column = minx(FILTER('Table','Table'[ID]=EARLIER('Table'[ID])&& 'Table'[Last modifty date]>EARLIER('Table'[Last modifty date])),'Table'[Last modifty date])

 

1.PNG

 





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

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.