Powerpivot Date table problem

[Ormondo_99]

Hi,

I'm really stuck on something and need some help urgently please. I have something to present to my CEO this week and I cant get my date table to work to compare data dates.

In short I have my power pivot hooked up to a SQL Stored procedure which extracts a number of fields however there are two fields I need to compare are
1. Logged Date
2. Resolved Date

The data I'm looking at are service desk incident data.


I need to have this on one single line chart chart to compare the number logged and the number resolved each month.

The problem I'm having is I can only connect one relationship to my date table at a time so my figures are out for the other. If I connect logged date colum to the Date Key column in the date table then I get the correct figures for logged. But if I also try and connect the Resolved date column the date key column in the date table then the relationship goes to "active No".

I'm using the the Azure date stream table for my date table.

What am i doing wrong as I beleive this should be pretty simple.

 

[Ormondo_99]

Thank you!!

It worked....Now I need to calculate the different between these vlaues and the estimate for forcasts but i'll figure that out.

 

[MD610]

Good! I'm glad it worked.

The difference is easy. Just add a 5th measure built off the 3rd and 4th:

[Delta]:=[LoggedIncidents]-[ResolvedIncidents]

As far as forecasting, there are many ways to go about it and they will depend on your actual data set, the trends within it, and the amount of history you have available. You'll just have to experiment.

Good luck!