KPI - Issue with calculating Backlog between Date

[Peems]

Hi every one,

First of all, sorry for my bad english, it's not my native langage 😕

I discoverd PowerBI Desktop 3 days ago,and i'm really lost about that tool's possibilities.

Before PowerBI, i used Excel.

I read many topics, tried lots of things without success. I can't do it on my own and i don't know if it's possible.

I'll try to explain my issue :

I use a connection to an old Ticketing DateBase (ITSM tool) to import all the tickets created (400 tickets per day) from 1 table since 2015.

Each ticket have a uniqueID,a creation date (recvddate),a close date (closedate),a status ("open", "reopen","closed"),and a groupname (Support N2, Technical N3, etc...).

Something like that :

ID	recvddate	closedate	status	groupname
1	01/12/2016	03/12/2016	Closed	Technical N3
2	10/01/2017		Open	Support N2
3	25/03/2017	26/06/2017	Closed	Marketing
4	26/04/2017	26/06/2017	Closed	Support N2
5	30/05/2017		Reopen	Security

I would like to know if i can,for one period (day,week,month,quarter,year) find the number of tickets which are open (or reopen),by groupname,on this date.

I used a Timeline and a "Line and clustered column chart"  :

"column" is the number of tickets created on this date.

"the line" is the Backlog : the number of tickets still opened on this date.

I'm doing well with column, the problem is Backlog..

Ex : Period 31/11/2016 - 01/06/2017

Support N2 = 2

Marketing = 1

Security = 1

I don't know if i'm really clear... Tell me if you need more explaination ..

Thank you in advance,

Regards,

Peems.

[v-shex-msft]

HI @Peems,

You can try to use below formula to calculate suitable records based on calendar.

Measure formula:

Suitable Group = 
var selected=ALLSELECTED('Date'[Date])
var currGroup=LASTNONBLANK(Sheet1[groupname],[groupname])
return
COUNTROWS(FILTER(ALLSELECTED(Sheet1),[groupname]=currGroup&&[recvddate]>=FIRSTDATE(selected)&&OR([closedate]>LASTDATE(selected),[closedate]=BLANK())))

Regards,

Xiaoxin Sheng

[Peems]

Hi o/

Thanks a lot v-shex-msft ! It's very helpful.

I'm wondering if we can have this same kind of result, but curving...

For exemple from 01/01/2016 to 31/12/2016, the backlog at the end of the year is 145.

The fact is that curve is linear on the period. 

Can we calculate it for each date (day, month, etc) between those 2 dates (To see during 1 period, evolution of Backlog) without changing dates ?

To obtain someting like that below (numbers are fake, it's just to show what i mean) ?

Thank you !

Regards,

Peems.

[TomMartens]

Hey,

there is a name for the task you are trying to solve: "event-in-progress"

Everything you need to know to solve your task can be found here:

https://blog.gbrueckl.at/2014/12/events-in-progress-for-time-periods-in-dax/

If you need further assistance, please describe what you have tried from the above post by Gerhard and provide a pbix file with sample data

Hope this helps

Tom 

[Peems]

Hi Tom,

I learned some article and tried some things, but i'm still a beginner to find by myself. I have to introduce another field in formula...

In my report, i have to count the evolution of "open tickets" between 2 dates, by groupname. 

I attached a pbix file.

For exemple, From Jan 2015 to June 2017, for groupname "N3 AVD FONCTIONNEL", Backlog in

June is 145 and should be 127 in May (in the same visual).

> Pbix file

I tried to use Gerhard's formula, but it dosen't work.

Thanks in advance,

Regards,

Peems.

[TomMartens]

Hmm,

thanks for uploading a sample file, unfortunately the download is blocked due to a virus alert.

Regards Tom

[Peems]

Hi Tom,

I uploaded file in .rar format.

Pbix file

That's better ?

Regards,

Peems.

[TomMartens]

Hmm, sorry but the problem with the rar archive.

[Peems]

Hi Tom,

And now ? > Link

[TomMartens · Accepted solution]

Hey,

here you find my solution, hopefully this works for you

The pbix contains two new pages

"Using RecvdDate" and "Using a separate Date Table"

The first uses just the table "Requete1", here the Measure is a little more complex, due to the levels of the RecvdDate column.

The second has a little more simpler Measure, but you have to calculate all the measures that you want to use in the visual, this is due to the fact that "new" Date table is unrelated.

[Peems]

Hi Tom,

I was on holiday... Far away from work.

So thank you so much, your first measure is just perfect !

I'll try to understand and use it on an other way.

Thank you again for your time 🙂

[TomMartens]

Passing all security checks, downloaded in subseconds

Flawless!

I will have a closer look during the next days.

[Peems]

Hi,

Sorry for my double post.. It was a registration mistake.

Thx Vvelarde for your answer.

You said than i can use : Open-Reopen = COUNTROWS ( FILTER ( Tickets, Tickets[status] IN { "Open", "ReOpen" } ) )

But I have the same resultat with this kind of column :  'Tickets'[CallStatus] <> "Fermé"

It just show me the number of tickets which have been open on a period, not tickets wich are still opened/reopen. 

In fact, i think i have to make a difference between Closeddate and CreateDate for each ticket, to know which ticket are still open on a date.

for exemple :

1

01/12/2016

03/12/2016

Closed

Technical N3

01/02/2016 : Open

02/02/2016 : Open

03/12/2016 : Closed

Even if status of the ticket today is "Closed".

 Maybe another table with all the date, with a calcul beween Closedate <> Createdate on each ticket (per groupname) ?