Compare Previous day records and comparing all previous days records with latest date

[Anonymous]

WIP Un-updated Cases Logic:
  1. There are 2 Logics included in this
a) All the WIP Un-updated cases which moved from previous day to today(This logic already built)  + All the un-updated WIP cases up to the previous day comparing with the all the WIP cases up to the Max date(Currently this logic is working manually, need to work it dynamically)

  Q1. First Logic: WIP Un-updated cases: Cases(Number in dataset) did not move from previous day to today
                 

     Q2. How we are considering the Un-updated WIP cases in Power BI: We are doing the lookup(means comparing or searching) from previous day WIP(Work in Progress) cases with the today  WIP cases, if the cases matches then flag as 1 otherwise 0, now we are counting the flag 0 cases, so that we will get the count of un-updated cases which moved from yesterday to today.

   Q3. Second Logic: How it working currently and giving the proper count

1. 1. We are storing all the un updated WIP cases in a separate table - Here the WIP Un updated cases will be stored as Max(date)-1(Which means one day before the max date) (Un-updated cases table)
2. 2. We are storing all the WIP cases in a separate table. (WIP cases table)
3. 3.  Now we are doing lookup for the Un updated cases table with the WIP cases table
   Ex: Currently we are comparing with Min Date(4th May) un updated cases with MaxDate-1(6th May)
             when we did the lookup for the above 4th and 6th dates, we got 603 cases which did not move from 4th may to 6th may.

   Now, since the count is partially correct in the fig 1. on 6th may, which is 589, because these are the cases got from previous day once which justifies the half part of the logic,

 but  when we do the comparison from 4th may with 6th may, we got again 603, so now on 6th may, we need to get the count of 589(which did not move from previous day) and then 603 count,  the un-updated cases from the start day itself to the day before the Max date.
 In this context on 6th May, 589+603=1192.
 

Now we can able to see the count as 1192 on 6th May, this is working fine when we do it manually,

The Issue is need to make it dynamically,

So for 7th may, 4th and 5th may WIP un-updated cases lookup with 7th may WIP cases, once we got the un-updated cases, then this un-updated cases should be added to the 631 count on 7th may, similarly for 8th, 9th and so on.

[lbendlin]

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. I cannot use screenshots of your source data.

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[Anonymous]

Number	Enquiry / Request	State	Updated
AB123	Request	WIP	2022-05-03 00:00:00
AB234	Request	WIP	2022-05-04 00:00:00
AB123	Request	WIP	2022-05-05 00:00:00
AB456	Request	WIP	2022-05-06 00:00:00
AB456	Request	WIP	2022-05-07 00:00:00
AB678	Request	WIP	2022-05-08 00:00:00
AB789	Request	WIP	2022-05-09 00:00:00
AB456	Request	WIP	2022-05-10 00:00:00
AB1011	Request	WIP	2022-05-11 00:00:00
AB789	Request	WIP	2022-05-12 00:00:00
AB1233	Request	WIP	2022-05-13 00:00:00
AB1344	Request	WIP	2022-05-14 00:00:00
AB1455	Enquiry	WIP	2022-05-15 00:00:00
AB1233	Enquiry	WIP	2022-05-16 00:00:00
AB1677	Enquiry	WIP	2022-05-17 00:00:00

I completely agree with you friend, sorry for that, i am glad that you are trying to help me. Please find the table, i tried to attach file, since i am not super i cannot do that, sorry for that, please let me know if any queries or you can stay in touch with me at lakshmikumar.kongaru@gmail.com. Thank you once again for the response.

[Anonymous · Accepted solution]

Hi @Anonymous ,

You can create a measure as below to get it:

WIP of Unupdated Cases =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Number] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[State] = "WIP"
            && 'Table'[Updated] <= SELECTEDVALUE ( 'Table'[Updated] )
    )
)

In addition, you can refer the following links to get the running total....

Running Total in Power BI

Running Total in Power BI

If the above ones can't help you solve the problem, please provide more sample data with Text format(exclude sensitive data) and your expected result with more details(scenario, special example and calculation logic etc.). It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

[Anonymous]

Thank you so much Friend, i will work on this and let you know shortly. Hoping this will work. Thank you so much for the response. 

[lbendlin]

Thank you for providing the sample data.  Does "un-updated" mean less than two updates? Or do you have another master list of request numbers? Please indicate the desired outcome.