Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to Solution.
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])
Proud to be a Super User!
@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.
Any suggestion, will be very helpful for us to move ahead.
Thanks In Advance,
Nayan
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?
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
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
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.
please see the attachment below.
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.
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?
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"))
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
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])
Proud to be a Super User!
User | Count |
---|---|
103 | |
88 | |
78 | |
71 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |