Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Counting Most Recent Status

The goal is to count distinct ID the most recent status. I was able to get a measure that would solve this in most cases but realized that some transactions can have multiple status' on the same day. Ultimately, the expected output should have 1 distinct ID + the most recent status. Below are the following: Sample Data (Measure being used / Table 1 = Current Output example / Table 2 = Expected Output example)

Any advice on how to rework the current measure to only count the most recent status with only 1 distinct ID? 

Sample PBIX > Sample PBIX Data 

Measure

 

 

Pattern Count.OppID = 
SUMX(
    SUMMARIZE(
       ALLSELECTED(Patterns),
        Patterns[OPP_ID], "Date",MAX(Patterns[DateInserted1])
    ), IF(
        [Date] >= CALCULATE(MIN(Patterns[DateInserted1]) )
        && [Date] <= CALCULATE(MAX(Patterns[DateInserted1]) )
        ,1, BLANK()
    )
)

 

 


Table 1= Current Output Example

OPP_IDSUMMARYOPP_STATUSOPP_VALUEDateInserted1 
697344Store Rollout: Shipped not SoldResolved - APS/Store78.84Thursday, November 5, 2020
697345Store Rollout: Shipped not SoldIn progress56.1Thursday, November 5, 2020
697346Store Rollout: Shipped not SoldNew58.86Thursday, November 5, 2020
697347Store Rollout: Shipped not SoldResolved - APS/Store112.89Thursday, November 5, 2020
697348Store Rollout: Shipped not SoldIn progress93.81Friday, November 6, 2020
697348Store Rollout: Shipped not SoldNew93.81Thursday, November 5, 2020
697349Store Rollout: Shipped not SoldIn progress84.33Thursday, November 5, 2020
713012Store Rollout: Shipped not SoldNew81.34Friday, November 13, 2020
713012Store Rollout: Shipped not SoldResolved - APS/Store81.34Friday, November 13, 2020
713013Store Rollout: Shipped not SoldNew89.41Friday, November 13, 2020
713013Store Rollout: Shipped not SoldResolved - APS/Store89.41Monday, November 23, 2020
713014Store Rollout: Shipped not SoldNew78.07Friday, November 13, 2020
713014Store Rollout: Shipped not SoldResolved - APS/Store78.07Monday, November 16, 2020
713015Store Rollout: Shipped not SoldResolved - APS/Store97.95Friday, November 13, 2020

Table 2 = Expected Output Example

OPP_IDSUMMARYOPP_STATUSOPP_VALUEDateInserted1 
697344Store Rollout: Shipped not SoldResolved - APS/Store78.84Thursday, November 5, 2020
697345Store Rollout: Shipped not SoldIn progress56.1Thursday, November 5, 2020
697346Store Rollout: Shipped not SoldNew58.86Thursday, November 5, 2020
697347Store Rollout: Shipped not SoldResolved - APS/Store112.89Thursday, November 5, 2020
697348Store Rollout: Shipped not SoldIn progress93.81Friday, November 6, 2020
697349Store Rollout: Shipped not SoldIn progress84.33Thursday, November 5, 2020
713012Store Rollout: Shipped not SoldResolved - APS/Store81.34Friday, November 13, 2020
713013Store Rollout: Shipped not SoldResolved - APS/Store89.41Monday, November 23, 2020
713014Store Rollout: Shipped not SoldResolved - APS/Store78.07Monday, November 16, 2020
713015Store Rollout: Shipped not SoldResolved - APS/Store97.95Friday, November 13, 2020

Any advice will be greatly appreciated! 

  • Anonymous's avatar
    Anonymous
    5 years ago

    I was able to correct the issue by changing the measure to reflect the date/time column. Funny how I spent so many hours trying to figure this out but after I posted this here and dwelling on it for 10 mins, I figured it out. 

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    I was able to correct the issue by changing the measure to reflect the date/time column. Funny how I spent so many hours trying to figure this out but after I posted this here and dwelling on it for 10 mins, I figured it out. 

    • v-easonf-msft's avatar
      v-easonf-msft
      Community Support

      Hi, Anonymous 

      It's pleasant that you can share your sloution to us. 

      Thanks for your sharing.Other community members  will easily find the solution when they get the same issue.

       

      Best Regards,
      Community Support Team _ Eason