New to PowerBI....Need Dax Help to create a rolling sum

[bussbuss]

Hi Guys,

I am pretty new to power Bi and I need some help with this issue i am encountering. So i have a table with a start date and an end date for when cases where opened or close. I need to show in my report for any start date, how many cases are still open (ie end date > start date or end date =blank) and then get a rolling sum of cases still open.

so far this is the approach that i have taken

i created a new column assigning a 1 to end date >start date else 0 (open cases 1

i created another colum assigning a 1 to end date =blank else 0 (open cases 2)

i created a column assigning a 1 to enddate <> blank (closed cases)

i created a column suming open cases 1+open cases 2 (total open cases)

what i need is the Total still Open Column

Date - Total Open Cases - Closed Cases - Total still Open

Jan 1 -  1                             0                            1

Jan 2 -   5                             3                            3 (5-3+1)

Jan 3 -   10                            12                         1 (10-12+3)

Any help will be greatly appreciated. Thank you so much

[v-chuncz-msft · Accepted solution]

@bussbuss,

You may take a look at the post below.

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