Forum Discussion

zgrshn's avatar
zgrshn
Regular Visitor
9 years ago
Solved

Opened closed dates

Hello

there are 3 columns in my Excel (Count , OpenedDate , ClosedDate)

I want to create a dash on PowerBi Desktop like this ,


 

How can I do it ?

Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi zgrshn,

     

    According to your description, I made a sample for your reference.

     

    I assume you have a table called "MyTestTable" like below.

    1. Add a new table called "StatusTable".

    2. Use the formula below to create a calculate column called "Status" in "MyTestTable" table.

    Status = IF(ISBLANK(MyTestTable[ClosedDate]), "Opened","Closed")

    3. Use the formula below to create a measure called "Value".

    Value =
IF (
    ISBLANK (
        CALCULATE (
            COUNTROWS ( MyTestTable ),
            FILTER (
                MyTestTable,
                MyTestTable[Status] = FIRSTNONBLANK ( StatusTable[Status], 1 )
            )
        )
    ),
    IF (
        FIRSTNONBLANK ( StatusTable[Status], 1 ) = "Opened",
        SUM ( MyTestTable[Count] ),
        0
    ),
    SUM ( MyTestTable[Count] )
)

    4. Use Matrix and Stacked column chart to show the data on the report.

    Here is the sample pbix file for your reference.

     

    Regards

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    9 years ago

    Can you explain where the numbers are coming from in your result?

    • zgrshn's avatar
      zgrshn
      Regular Visitor
      9 years ago

      I write the numbers manually in Excel .

       

       

       

       

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

        Hi zgrshn,

         

        According to your description, I made a sample for your reference.

         

        I assume you have a table called "MyTestTable" like below.

        1. Add a new table called "StatusTable".

        2. Use the formula below to create a calculate column called "Status" in "MyTestTable" table.

        Status = IF(ISBLANK(MyTestTable[ClosedDate]), "Opened","Closed")

        3. Use the formula below to create a measure called "Value".

        Value =
IF (
    ISBLANK (
        CALCULATE (
            COUNTROWS ( MyTestTable ),
            FILTER (
                MyTestTable,
                MyTestTable[Status] = FIRSTNONBLANK ( StatusTable[Status], 1 )
            )
        )
    ),
    IF (
        FIRSTNONBLANK ( StatusTable[Status], 1 ) = "Opened",
        SUM ( MyTestTable[Count] ),
        0
    ),
    SUM ( MyTestTable[Count] )
)

        4. Use Matrix and Stacked column chart to show the data on the report.

        Here is the sample pbix file for your reference.

         

        Regards