Week over Week Comparison

[cmcreynolds]

I am posting here because I grab my data using Power Query do some simple rates in PowerPivot's data model and report in a PP Pivot table.

So, the report as it stands is dynamic - it pulls directly from Salesforce and spits out this fantastic pivot table with slicers and a timeline.

Now, a CEO has asked me to have a week to week comparison. I thought I had started a grasp on PQ and PP but this request has blown my mind. (I feel this kind of metric is pointless since last week has already happened and we can't change that...but I digress...)

So, I would assume I'd have to "freeze" every week's data, making sure I have the date to tie to a week number (in my calendar table already, thank goodness). But, how do I (or how can I) still utilize the same slicers and timeline? Won't there be a lot of zeroes popping up?

Anyway, and suggestions as to do this with PowerQuery and PowerPivot?

 

[scottsen]

Can you paste a bit of sample data?

In general, you are going to have something like...

[NumTransactions] := COUNTROWS(Transactions)
or whatever, Total Sales, etc

[NumTranactions - Prior Week] := CALCULATE([NumTransactions], DATEADD(Calendar[DateKey], -7, day))

[Num Trans - % Change vs Prior Week] := blah, blah... do some division.

 

[cmcreynolds]

So, there's no need for a percent calculation, so the division is out. It's just a count from one week to the next. Then, yes, the subtraction.

Now, the counts I make are based on dates - so, I'm thinking have two tables, one filtered "Past week" (or 7 days or whatever) and the other before that? Using timelines, I guess? Wouldn't I be able to keep it dynamic that way? (In other words, I wouldn't have to freeze data?)

 

[scottsen]

I think I am starting to understand your ask Are you saying you don't HAVE last weeks data?

 

[cmcreynolds]

It's just cumulative. I'm reading this directly from Salesforce Objects.