Need help creating date delta columns

[warrencowan]

I have a value in a column, eg (visits), and in another column I have the (date). I also have 3 rows for each date, because I have 3 different stores (StoreID) with a vlaue for visits

I want to be able to create a delta column that counts the difference between the visits for the row date, and the last previous date,  for each storeID row.

I'm reading the DAX guides but they seem different from the Power query descriptions, whcih are different again in the powerbi desktop.

Any help greatly appreciated!

[warrencowan]

Hey smoupre, no that what was my gaff. Trying to be to clever for my own good and screwed up the table example. Each city should be on a unique date row.

Eg

01/01/15 manchester
02/01/15 Manchester
03/01/15 Manchester
01/01/15 London
02/01/15 London
Etc etc

[konstantinos]

Hope this is similar to what you need. Assuming you trying PowerBI Desktop.

First you need to create a Date table in order to have time intelligence measures. Go to the queries pane and select the query & right click - > duplicate or reference the query -> select the date column and remove all others -> remove duplicates in dates -> load.

Now you have 2 tables in data model -> create relantionship between the date columns .

Write the folowing measure ( only works in PBI Designer or excel 2016 preview else you need to create 3 measures - each for every variance) : 

Delta :=

VAR totalvisits =SUM ( Table[Visits] )

VAR previousvisits =CALCULATE (SUM ( Table[Visits] );PREVIOUSDAY('calendar'[Date]))

RETURN DIVIDE ( totalvisits - previousvisits ; previousvisits )

Drag the date from the calendar table & and the store from your visits on the report..It should look like this 

[warrencowan]

I think I've followed your instructions to the letter, but unfrotunately not got a result. The formula validates so that seems great, but I have no values in the new measure.

My data might be more convoluted than I had first suggested.

Basically I have date like this...and my dates are all week commencing dates, not daily's, so I'm unsure if my single date table or my lack of linear dailys is the reason. Any further thoughts?

City	Store	Campaign	WeekCommencingDate	Visits	Delta Vists
London	1	Print	01/01/2015	10	?
London	1	Radio	01/01/2015	8	?
London	2	Print	01/01/2015	5	?
London	2	Radio	01/01/2015	8	?
London	3	Print	01/01/2015	9	?
London	3	Radio	01/01/2015	8	?
Leeds	1	Print	01/01/2015	10	?
Leeds	1	Radio	01/01/2015	8	?
Leeds	2	Print	01/01/2015	5	?
Leeds	2	Radio	01/01/2015	8	?
Leeds	3	Print	01/01/2015	9	?
Leeds	3	Radio	01/01/2015	8	?
London	1	Print	02/01/2015	9	?
London	1	Radio	02/01/2015	4	?
London	2	Print	02/01/2015	6	?
London	2	Radio	02/01/2015	7	?
London	3	Print	02/01/2015	2	?
London	3	Radio	02/01/2015	6	?
Leeds	1	Print	02/01/2015	9	?
Leeds	1	Radio	02/01/2015	4	?
Leeds	2	Print	02/01/2015	6	?
Leeds	2	Radio	02/01/2015	7	?
Leeds	3	Print	02/01/2015	2	?
Leeds	3	Radio	02/01/2015	6	?

[konstantinos]

Hope you find a solution with lookupvalue() as @andre suggested.

Regarding my solution, it won't work without having all dates in Date table..and the dates for visits are weekly ( my formula checks the previous day) 

From your first post you can actually do it in DAX or PowerQuery. 

1. My solution: "Add Index Column" in the reference query date you created load it and replace the delta measure second VAR with :

VAR previousvisits =CALCULATE (SUM ( Sheet1[Visits] );FILTER (ALL ( 'calendar' );'calendar'[Index]= MAX ( calendar'[Index] ) - 1))

If that not works check the links below..

For formatting DAX formulas (easier to look & understand) http://www.daxformatter.com/

2.A date dimention table will always be useful so - for creating a Date table take a look at this http://www.excelguru.ca/blog/2015/06/24/create-a-dynamic-calendar-table/

3.You can create the delta in PowerQuery by modifying a bit (change some functions)  this post http://www.powerquery.training/portfolio/time-intelligence-with-power-query/

4.Else if you need to do it in DAX, you will need a Date or Custom Date table. There is a really great article & site http://www.daxpatterns.com/time-patterns/

Hope that helps...

[warrencowan]

Thanks Konstantinos. I will research your steps and pointers!

[warrencowan]

Thanks Konstantinos, thats a very helpful and detailed example. I will give it a shot and report back

[andre · Accepted solution]

you may also take a look at lookupvalue() function ...

[warrencowan]

Thanks Andre. I have experiemnted with this, but I think the last criteria in the lookup query has to be a non column item.

So I can't lookup [visits] where [date] is (datediff [date] -7) for example.

Any thoughts how to work around that?

[andre]

Can you add a different column and populate it with logic in such a way that it helps you get the lookupavlue() function to work?

[warrencowan]

This is my data structure btw before anyone asks 

Date	Visits	Store	Daily Delta
01/01/2015	3	London	?
01/01/2015	2	London	?
01/01/2015	3	London	?
02/01/2015	5	Leeds	?
02/01/2015	6	Leeds	?
02/01/2015	1	Leeds	?
03/01/2015	3	Manchester	?
03/01/2015	4	Manchester	?
03/01/2015	1	Manchester	?

[Greg_Deckler]

Did you really mean to have all the London rows be the same date, all the Leeds rows be the same date, etc.? Or did you mean for each date to have a London row, Leeds row and Manchester row?

It would seem that this would be a good case to user EARLIER but only if your data is sorted on import by Store and Date and even then it is going to mess up when transitioning between stores, unless you imported each store as a separate table.