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

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

    • zgrshn's avatar
      zgrshn
      Regular Visitor
      9 years ago

      Dear JerryLi

      Thank you very much 

    • electrobrit's avatar
      electrobrit
      Post Patron
      9 years ago

      v-ljerr-msft

      Really like what you've done, easy to understand.

       

      Can you help a bit further with that formula (VALUE) if there are 4 status values? I don't have a count column, I just have a status column for each ticket with the following indicator: open, closed, pending and resolved.

      (FYI-resolved and closed mean 2 different things)

       

      Thank you in advance!