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

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:

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:

Regards,

Mal