Forum Discussion

rocky09's avatar
rocky09
Solution Sage
8 years ago
Solved

Issue with Summarizing Status Count

See below table, I have cat. column, where Users start working on each cat. and change the status to Inprogress, Complete and some times rework.

 

So, if a Cat. worked on 5 days and status will be In progress for 5 days, on 6th day , the work was completed. So, the status will becomes Completed.  Now, I am trying to count of Status.

 

Logic is:

 

* If any of the Cat. has Completed. The Cat. will show under Completed and it should counts only one.

* If any of the Cat. is still In Progress, the Cat. will show InProgress status and it should take count as one every it has 10 Previous inprogress days.

*Same logic appicable to rework.

 

See my sample data and the solution i am expecting.

 

DateCat.Status
2-Oct-17alpha_9383993In Progress
3-Oct-17Pulse_9387388In Progress
4-Oct-17Pulse_9387388Rework
5-Oct-17alpha_9383993In Progress
6-Oct-17alpha_9383993Completed
7-Oct-17Pulse_9387388Completed
8-Oct-17Oppo_tes_9383In Progress
9-Oct-17Oppo_Max_8977Rework

 

StatusCount
Completed2
In Progress1
Rework1
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    Hi rocky09,

     

    Based on my test, the formula below should work in your scenario.

    Count = 
VAR maxDate =
    CALCULATE (
        MAX ( Data[Date] ),
        FILTER ( ALL ( Data ), Data[Cat.] = EARLIER ( Data[Cat.] ) )
    )
VAR minDate =
    CALCULATE (
        MAX ( Data[Date] ),
        FILTER ( ALL ( Data ), Data[Cat.] = EARLIER ( Data[Cat.] ) )
    )
RETURN
    IF (
        Data[Status] = "Completed",
        1,
        IF ( Data[Date] = maxDate, 1 + DATEDIFF ( minDate, maxDate, DAY ) / 10, 0 )
    )

     

    Here is the sample pbix file for your reference. :smileyhappy:

     

    Regards

15 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    8 years ago

    Hi rocky09,

     

    Based on my test, the formula below should work in your scenario.

    Count = 
VAR maxDate =
    CALCULATE (
        MAX ( Data[Date] ),
        FILTER ( ALL ( Data ), Data[Cat.] = EARLIER ( Data[Cat.] ) )
    )
VAR minDate =
    CALCULATE (
        MAX ( Data[Date] ),
        FILTER ( ALL ( Data ), Data[Cat.] = EARLIER ( Data[Cat.] ) )
    )
RETURN
    IF (
        Data[Status] = "Completed",
        1,
        IF ( Data[Date] = maxDate, 1 + DATEDIFF ( minDate, maxDate, DAY ) / 10, 0 )
    )

     

    Here is the sample pbix file for your reference. :smileyhappy:

     

    Regards

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    Hi rocky09

     

    One way of doing this .

    In essence you want the count based on last status for each cat

    First Add a calculated Column lets say "Last Date"

    =
CALCULATE (
    MAX ( Table1[Date] ),
    FILTER ( table1, Table1[Cat.] = EARLIER ( Table1[Cat.] ) )
)

     Then another calculated Column named "Count"

    =
IF ( Table1[Date] = Table1[Last Date], 1, 0 )


    Now Pivot with Status in Rows and Sum of Count in Values

    • rocky09's avatar
      rocky09
      Solution Sage
      8 years ago

      Thank you for your kind reply.

       

      However, It is not counting properly. It is ignoring some of the Completed, Inprogress and Rework.

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Community Champion
        8 years ago

        Hi rocky09

         

        After adding the 2 calculated columns, create a pivot table with "Status" on Row Field and "Count" on Value field

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    Hi,

     

    I have been able to solve this problem with a single calculated field formula.  Please allow me time until tomorrow to share my solution.  In the meantime, here is a screenshot of my solution