Opened closed dates

[zgrshn]

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 ?

[Greg_Deckler]

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

[zgrshn]

I write the numbers manually in Excel .

[v-ljerr-msft · Accepted solution]

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

[electrobrit]

@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!

[zgrshn]

Dear JerryLi

Thank you very much