cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

Help with Column Calculation

Hi there,

 

I'm trying to visualize our service desk backlog (daily - trended over time)

 

Below screenshot shows the data I have in the "Data" data table. I also have another table called "Date" which has a list of the calendar dates. Screenshot also shows the calculation I believe will work to give me the number of backlogs given the calendar date.

 

Capture.PNG

 

The calculation steps:

  1. Count the number of calls with log date = calendar date ---> (Column G)
  2. Count the number of calls with resolve time = calendar date --->  (Column H)
  3. Net count (Column I) = Column G - Column H
  4. Back log = Cumulative of Column I

Now I want to create a visualisation where I use the Calendar date to graph Column I

 

Can someone kindly guide me what I need to in Power BI to achieve the calculation steps that will allow me to create the graph?

 

Thank you in advance for your help

Wes

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Resolver III
Resolver III

Re: Help with Column Calculation

Hi Wes,

 

Assuming you have already created a .pbix file in PowerBI and connected to the data, here are some steps to follow:

 

1. In the Query editor, make sure you have formatted Log Date and Resolve Time fields as 'Date' fields, not 'Date Time'

2. Create a relationship between your Date field and your Log Date field.

3. Create a second relationship between your Date field and your Resolve Time field. It will be inactive (because you can only have one active relationship at a time, but we will use it later.)

4. Add the following measures (note: your fields names might be slightly different, but you should get the idea):

 

Opened = COUNTROWS(Data)

Resolved = CALCULATE(COUNTROWS(Data),
	USERELATIONSHIP('Calendar'[Dates], Data[Resolve Time]))

Net Count = [Opened] - [Resolved]

Backlog = CALCULATE([Net Count], 
	Filter (ALL('Calendar'), 'Calendar'[Dates] <= MAX('Calendar'[Dates])))

5. Create your visualizations. A Waterfall chart of Net Count and Dates would work. Or you can do a line or column chart of the Backlog measure. 

 

regards,

 

Mal

View solution in original post

Highlighted
Resolver III
Resolver III

Re: Help with Column Calculation

Hi Wes,

 

The approach I took was slightly different to the one that @Phil_Seamark showed you, so if you are trying to combine the two it might not work very well.

 

My approach is not as sophisticated (mainly because I would struggle to come up with the table creation code that Phil did!), but still worked for me.

 

Perhaps you changed the Resolve Time format in the Query Editor, but not in the main Power BI window? To check, click on Resolve Time field on the right of screen, then click on the Modelling tab and check Data Type:

 

Call backlog 2.png

 

If that doesn't work, here are the steps I followed. Perhaps you can spot what you did differently:

 

1. Gather your data in a format that works for you. In this case I created a spreadsheet with a sheet called Data. It contained the Call Number, Log Date and Resolve Time data in columns. 

2. I also created a Calendar table. There are several ways to do this, but I find the easiest is to create another sheet in your spreadsheet called Calendar, and create a column called Dates.  Enter 1 Jan 2015 in the first cell and fill down until 31Dec 2015. (Note that, this isn't really the best way, because it requires manual work/maintenance - but it will do for now) 

3. In PowerBI, click Get Data > Excel and select the spreadsheet created above. Then select the Data and Calendar sheets.

4. Edit the queries and make sure the 3 date fields (Calendar[Dates], Data[Log Date] and Date[Resolve Time]) are all set to Date format (not Date/Time)

5.  Create the relationships, measures, etc. as per my last post.

 

For me, the relationships look like this:

 

Call backlog.png

 

Regards,

 

Mal

View solution in original post

12 REPLIES 12
Highlighted
Microsoft
Microsoft

Re: Help with Column Calculation

HI @wes-shen-poal

 

If you create a new calculated table using the following code

 

New Table = 
Var Dates = SELECTCOLUMNS(CALENDARAUTO(),"DateKey",[Date])
RETURN  UNION(SELECTCOLUMNS(
                    FILTER(CROSSJOIN(Dates,Data),[DateKey] = [Log Date]),
                    "DateKey",[DateKey],
                    "Call Number",[Call Number],
                    "Logged",1,
                    "Resolved",0
                    )
                ,SELECTCOLUMNS(
                    FILTER(CROSSJOIN(Dates,Data),[DateKey] = [Resolve Time]),
                    "DateKey",[DateKey],
                    "Call Number",[Call Number],
                    "Logged",0,
                    "Resolved",1)
                )

This will create a table that looks like this

 

 

logged.jpg

 

Then you can create the following 4 measures 

 

Count of call with log date = CALCULATE(SUM('New Table'[Logged])) 

Count of call with log date = CALCULATE(SUM('New Table'[Logged])) 

Net Count = [Count of call with log date] - [Count of call with resolve time]

Backlog = CALCULATE(SUMX('New Table',[Logged]-[Resolved]),FILTER(ALL('New Table'),[DateKey]<=MAX([DateKey])))


 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Highlighted
Microsoft
Microsoft

Re: Help with Column Calculation

Hi @wes-shen-poal

 

This is the matrix I was able to create using the above approach

 

logged2.jpg


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Highlighted
Helper III
Helper III

Re: Help with Column Calculation

Hi Phil,

 

Thanks so much for your help. I would've never gotten this far alone... >.<

 

How do I change the data type of Call Number to text. At the moment it's numeric, and is summing everything

 

Thanks

Wes

Highlighted
Helper III
Helper III

Re: Help with Column Calculation

Oh go it! I just had to refresh the data table.

 

Thanks

Wes

Highlighted
Resolver III
Resolver III

Re: Help with Column Calculation

Hi Wes,

 

Assuming you have already created a .pbix file in PowerBI and connected to the data, here are some steps to follow:

 

1. In the Query editor, make sure you have formatted Log Date and Resolve Time fields as 'Date' fields, not 'Date Time'

2. Create a relationship between your Date field and your Log Date field.

3. Create a second relationship between your Date field and your Resolve Time field. It will be inactive (because you can only have one active relationship at a time, but we will use it later.)

4. Add the following measures (note: your fields names might be slightly different, but you should get the idea):

 

Opened = COUNTROWS(Data)

Resolved = CALCULATE(COUNTROWS(Data),
	USERELATIONSHIP('Calendar'[Dates], Data[Resolve Time]))

Net Count = [Opened] - [Resolved]

Backlog = CALCULATE([Net Count], 
	Filter (ALL('Calendar'), 'Calendar'[Dates] <= MAX('Calendar'[Dates])))

5. Create your visualizations. A Waterfall chart of Net Count and Dates would work. Or you can do a line or column chart of the Backlog measure. 

 

regards,

 

Mal

View solution in original post

Highlighted
Helper III
Helper III

Re: Help with Column Calculation

Hi @MalS

 

Thanks so much, all the measures you've asked me to create is working except for "Resolved"

 

I've created a non-active relationship between Date and Resolve Time, and changed the data type to Date (not Date Time) as per your instructions but unfortunately it doesn't look like it's picking up the relationship. Any further suggestions?Capture.PNG

 

Thanks

Wes

Highlighted
Resolver III
Resolver III

Re: Help with Column Calculation

Hi Wes,

 

The approach I took was slightly different to the one that @Phil_Seamark showed you, so if you are trying to combine the two it might not work very well.

 

My approach is not as sophisticated (mainly because I would struggle to come up with the table creation code that Phil did!), but still worked for me.

 

Perhaps you changed the Resolve Time format in the Query Editor, but not in the main Power BI window? To check, click on Resolve Time field on the right of screen, then click on the Modelling tab and check Data Type:

 

Call backlog 2.png

 

If that doesn't work, here are the steps I followed. Perhaps you can spot what you did differently:

 

1. Gather your data in a format that works for you. In this case I created a spreadsheet with a sheet called Data. It contained the Call Number, Log Date and Resolve Time data in columns. 

2. I also created a Calendar table. There are several ways to do this, but I find the easiest is to create another sheet in your spreadsheet called Calendar, and create a column called Dates.  Enter 1 Jan 2015 in the first cell and fill down until 31Dec 2015. (Note that, this isn't really the best way, because it requires manual work/maintenance - but it will do for now) 

3. In PowerBI, click Get Data > Excel and select the spreadsheet created above. Then select the Data and Calendar sheets.

4. Edit the queries and make sure the 3 date fields (Calendar[Dates], Data[Log Date] and Date[Resolve Time]) are all set to Date format (not Date/Time)

5.  Create the relationships, measures, etc. as per my last post.

 

For me, the relationships look like this:

 

Call backlog.png

 

Regards,

 

Mal

View solution in original post

Highlighted
Helper III
Helper III

Re: Help with Column Calculation

Hi both

@MalS@Phil_Seamark

 

Thank you both so much for your tremendous help. 

 

@MalS, yes you are correct, I just needed to change the data type in the front end rather than just the back end.

 

Thanks again

Wes

Highlighted
Helper III
Helper III

Re: Help with Column Calculation

Hi @MalS

 

Thanks again for your help with this one. Just one more question.

 

Is there any way I can calculate backlog based on whatever date range I've specified with my date filter?

 

As you can see from screenshot below, if I narrow my date range down to 07/05/2016 - 05/06/2016, my backlog calculation does not reflect the context of the date range specified... i.e. Backlog row 1 should = 3, row 2 should = 5...etc.

 

Are you able to provide guidance on how I can achieve this?

 

Thanks again.

Wes

 

Capture.PNG

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors