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 ...
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

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

      • rocky09's avatar
        rocky09
        Solution Sage
        8 years ago

        Hi,

         

        I am getting Completed count properly. But, for In-progress, I am getting all count. It should count only latest Status.