Create graph from a calculated table

[ToMaTtAcK]

Hi,

I work at the service desk and I have a table with all cases with creation date, closed date etc...

I would like to have an overview of the backlog thru time.

I do this mannually for now. (have an excel where I fill in every 2 weeks the result of a query = count open tickets)

I could not figure out how to do that in Power Bi.

So, for each date (let's say each 14 days) i would calculate the open tickets.

This should create a list of data, that I want to see in a graph.

Table1=Cases

Table2=Backlog

So I though I create a new table in excel and import this where I just generated a list of dates =Backlog[gen_date] that I can use to calculate.

In the following column I would do the actual calculation from the table of the cases.

to make it easy let's say the query would be something like : give me the sum of the Cases[ID] with Cases[status] = open and where Cases[created date] > Backlog[gen_date]

But i'm unable to figure this one out.

Any help is appreciated 🙂

[v-chuncz-msft · Accepted solution]

@ToMaTtAcK,

You may take a look at the following post.

https://community.powerbi.com/t5/Desktop/calculate-open-tickets-by-date/m-p/207644#M91517