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

rocky09's avatar
rocky09
Solution Sage
8 years ago

Thank you so much.  Waiting for your kind reply.

Ashish_Mathur's avatar
Ashish_Mathur
Super User
8 years ago

Hi rocky09,

 

You may refer to my solution here.

 

Hope this helps.

  • rocky09's avatar
    rocky09
    Solution Sage
    8 years ago

    Hi Ashish,

     

    Thank you,  it is in excel. I am looking for a solution in Power Bi Desktop.

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      8 years ago

      Hi rocky09,

       

      You can just import the data to PowerBI desktop.  File > Import > Excel workbook.

      • rocky09's avatar
        rocky09
        Solution Sage
        8 years ago

        I have older version of excel. So, I cannot see the measure you used in the excel. That is why i have asked you to provide pbi file.