Day over Day - Freezing data?

[cmcreynolds]

Okay - so the inevitable has happened: I've been asked to compare data day over day ("progress"). I at least managed to get my bosses to understand they want "last full day" compared to the "previous full day" instead of today versus yesterday.

I am connecting to Salesforce (in case anyone cares) and those records change day to day - one record can go from "inquiry" to "prospect" - is there a time field? YES! BUT, some are manually entered. (Example, Sarah gets an e-mail on Wednesday saying the application was received last Friday, so the field is populated with Friday's date) Therefore, the date fields aren't really reliable.

So, I was thinking the best solution would be to download the data, freeze it and do the same the following day - then, I'd compare the two. BUT, how can I do this as an ongoing process? Is there a way to "discard" older date freezes? (My boss just wants yesterday compared to the day before - again, "progress")

I don't need code right now, just some conceptual ideas. Maybe I don't use PQ or PP? Just Excel?

Added super bonus - I need to exclude weekends. (So, on Tuesday, I would be looking at data from Monday and Friday).

I think this is going to incorporate VBA or Macros, which I'm very unfamiliar with, so be gentle, kind MrExcel-ers!

Thank you!

 

[cmcreynolds]

I completely agree - I can totally give him "the way the world is." Unfortunately, he wants the delta as performance measures: "Look how well we did yesterday compared to the day before!" I personally don't like that philosophy, but that's what he's asked for. I wish it were simpler, but it doesn't look like I can get around Access/SQL. I have some basic understanding and usage of Access, but I don't use it regularly (yet).

Luckily, the data isn't "big" - it's only about 20k rows x 20 columns every day. So, maybe Access won't explode

Thanks, Matt for your help.

 

[Matt Allington]

Maybe I still don't understand. I consider "look how well we did yesterday" to be completely different to what I have Ben talking about here "changing versions of truth". Let me explain with 2 examples.

Changing versions on of the truth.
Today the manager of this store is Peter
last year the manager of this store was Mary
if you want your reports to show who the manager was at any point in time, then you need to store the data. This specific example is called a "slowly changing dimension". My understanding of your example was at it was like this (not the same but similar). In your case someone was changing a date. My understanding was because the date was wrong or just different now. I think that understanding may have been incorrect.

Look how well we did yesterday
Today we have 12 prospects worth a potential of $1m per year
yesterday we had 14 prospects worth a potential of $1.2m per year
what the hell are you sales guys doing?

this is a regular out of the box Powerpivot reporting project with nothing unusual.

Given your data sizes, just extract the data every month and report on the changes. Given you don't want to go for Access, I suggest a month extract to a file and then use power query to combine. Read my blog series here Combine Excel Workbooks with Power Query – Method 1 -

Maybe repost some examples and I can give it a fresh look.

 

[cmcreynolds]

So, what my boss is wanting is more like your "Look how well we did yesterday" example (Except we want full days, so Yesterday compared to two days earlier).

Think of Salesforce as a very complex Rolodex - but the way our Admissions department set it up was thinking like a "funnel" - and certain things are triggered in the record when the funnel stage was set up.

For example, On 3/1/2016, Sue Smith is in our records as an "Inquiry." But on 3/2/2016 we receive her application and write down the date in a field called "appreceived." Once that field is filled in, her status of "Inquiry" is automatically changed to "Applicant"

What my boss wants is a count of both Inquiries and Applicants (separately) from 3/2/2016 compared with the count of Inquiries and Applicants from 3/1/2016. So, the Iquiries would be one fewer on 3/2/2016 and Applicants one greater (assuming Sue is the only one whose application was received). So, my boss would want something like:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Day1 (3/1/2016)
[/TD]
[TD]Day2 (3/2/2016)
[/TD]
[TD]Delta
[/TD]
[/TR]
[TR]
[TD]Inquiries
[/TD]
[TD]200
[/TD]
[TD]199
[/TD]
[TD]-1
[/TD]
[/TR]
[TR]
[TD]Applicants
[/TD]
[TD]50
[/TD]
[TD]51
[/TD]
[TD]+1
[/TD]
[/TR]
</tbody>[/TABLE]


NB: not all of the 51 Applicants would have their applications in on 3/2/2016.

If there were timestamps of those automatic triggers, this would be a whole different ballgame.

Did that help?

 

[Matt Allington]

Yes this confirms that my previous understanding was wrong. I thought you were originally saying the date changed because of a mistake. Now I understand the day is changing because of changing internal assessment of the situation. This is standard dax.

Just do monthly exports as I mentioned, use power query to combine the data, then start to write the dax. Make sure there is a date stamp for each extract and connect that to your date table. Don't connect the other date fields to the date table. At least to start you should easily be able to create measures like "count of prospects with status x"

 

[cmcreynolds]

So I can do this in just PQ?

 

[Matt Allington]

Power Query will allow you to load data from multiple excel or CSV files into a single data table in Power Pivot. While it is technically possible to do this if each file is different, it is much better/easier if each file extract is identical. Follow the instructions I posted earlier at my blog and you will see.

 

[cmcreynolds]

So, I know how to query folders containing CSV, TXT, and XLS files and I know how to include a timestamp in the PowerQuery pull. But where I'm experiencing conceptual confusion is the "function" part in your blog. I think once I get how to save the file every day (automatically via PowerBI, maybe?) using PQ, I will be able to build the Day over Day report.

 

[Matt Allington]

You only need the function approach if the file needs to be reshaped. If you files are already well shaped ( in tables like you need in power bi, you can use the other method.

 

[cmcreynolds]

Could you provide the link to the second method? I do think the files are already well-shaped.

 

[Matt Allington]

The article is linked at the bottom of the post I provided above.

Here it is. Read this one first Combine CSV Files with Power Query -
And then at the bottom of that article is a link to the way to do it with excel files. Reading the csv way first will make the Excel method easier to understand.