Hi Fellow Power BI Users -
I followed your instructions creating the measures for the sales trend analysis example you presented, and it successfully returns sales deltas by customer, however where your delta is changing over time, my delta shows as zero. This seems like a problem regarding context. Maybe it is calculating no delta because it is not evaluating at the customer level?
Thank You,
Nick
Hey Nick,
Can you should me the formula and model (expecially where you think this is falling over)
I can almost guarantee it’s context related, because that’s what always brings zeos.
Certainly first try to visualize what’s happening with the filters in your model before any calculation is taking place.
Really you shouldn’t be using dates in you fact table to run this analysis. It should all come from a dates table that has a relationship down to your loan table.
Time intelligence functions are optimized to work over a date table with sequential dates.
I would also always place both of these initial calculations into a table, so you can see there behaviour when you select things and change the initial context.
This has to be a model related issue, but hard to say as I can’t see the model and the relationship you have.
I highly highly recommend using the dates column in a date table here. This will make a lot more sense if you set it up this way.
You see in nearly all my time intelligence examples. I always use the date table and dates column in any formula.
Hi Sam,
I’m using dates from the date table. The date table I’m using extends into the future. I wonder if that might be causing the issue. Also, it seems like I need to be telling the function to calculate variances at the Sponsor/Customer level using virtual in memory tables. Does this sound right?
Thank You,
Nick
Are you really using the date column? How come it’s called “Actual Loan Date”. Probably better to keep this simple as ‘Dates’
All I can think of other than this is that the relationships are incorrect in your model.
This is a pretty simple calculation so it’s odd.
Were you able to get this into a table to audit the numbers easier and also break out each individual calculation to see how they change for any adjustment in initial context?
Hi Sam,
There are more than 1 date table, so to avoid confusion, we named them each uniquely. I figured out the issue. We were using a date table that uses a surrogate date key. By setting the date as the date key, the issue is resolved. Time intelligence functions prefer it, apparently.
Thanks!
Nick
Ok cool, glad it’s sorted out.
