How do I calculate how many open items I have to graph over time?

[Anonymous]

How do I calculate how many open items I have to graph over time?

The calculation would need to be able to determine how many items had been created so far (using a Submission Date) but then remove any items that had been closed or canceled so far (using Closed Date and Cancelled Date).

It's easy to calculate what it is now, but I don't know how to write it so that it's can be applied over time.

Help!

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

I am not sure whether the below scenario is what you are thinking about.

Please kindly check the below picture and the DAX measure.

Date table and Data table are connected via three connections and all are inactive.

The DAX measure can be altered based on where the blank dates are in the DATA table.

The Pbix file link is down below.

items in progress =
CALCULATE (
     COUNTROWS ( data ),
     FILTER (
     VALUES ( data[SubmissionDate] ),
          data[SubmissionDate] <= MAX ( dates[date] )
     ),
     FILTER ( VALUES ( data[ClosedDate] ), data[ClosedDate] > MIN ( dates[date] ) )
)
+    CALCULATE (
     COUNTROWS ( data ),
     FILTER (
          VALUES ( data[SubmissionDate] ),
          data[SubmissionDate] <= MAX ( dates[date] )
      ),
      FILTER (
          VALUES ( data[cancelledDate] ),
          data[cancelledDate] > MIN ( dates[date] )
     )
  )

https://www.dropbox.com/s/mtxna8xj66x8ciq/malorian%202021%200323.pbix?dl=0 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

[negi007]

@Anonymous pl. share your sample dataset so that appropriate result can be provided to you.

[Anonymous]

I have added it