Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
wes-shen-poal
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
MalS
Resolver III
Resolver III

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

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
wes-shen-poal
Helper III
Helper III

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

MalS
Resolver III
Resolver III

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

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

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

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

You can do this with a second 'disconnected' date table and a filter that controls just the backlog calculation time. Unfortunately it's not very elegant, because it means you need two date filters on screen at once (once to control the backlog calc and one to control the dates displayed on the table).

 

Perhaps there is a better way, but I can't think of one right now...

 

In the meantime:

 

  1. add a second calendar table to your .pbix file (i have called it "Calendar (2)" below). 
  2. make sure it has no relationships to any other tables
  3. Add this measure (or replace your existing Backlog measure):
Backlog 2 = CALCULATE([Net Count], 
			FILTER(ALL('Calendar'), 
				'Calendar'[Dates] <= MAX('Calendar'[Dates]) 
				&& 'Calendar'[Dates] >= MIN('Calendar (2)'[Dates])
				))

Then add a new 'Calendar (2)'[Dates] slicer.  The smallest date in that slicer will be used as the starting date for the backlog calcs.

 

Regards,

 

Mal

 

Thanks again @MalS for your help.

 

I looked into my options a bit more and discovered that, if I changed FILTER(ALL()) to FILTER(ALLSELECTED()) then my date filter will work

 

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

 

 

Excellent - that's much better than my suggestion. 

 

I had tried adding ALLSELECTED to the measure too, but couldn't get it to work - so thought it wasn't possible.. Guess I messed up the syntax somehow...

 

Anyway, thanks for letting me know about your solution.

Phil_Seamark
Employee
Employee

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!

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

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

 

Thanks

Wes

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.