Forum Discussion
Need help creating date delta columns
you may also take a look at lookupvalue() function ...
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 | 01/01/2015 | 10 | ? | |
| London | 1 | Radio | 01/01/2015 | 8 | ? |
| London | 2 | 01/01/2015 | 5 | ? | |
| London | 2 | Radio | 01/01/2015 | 8 | ? |
| London | 3 | 01/01/2015 | 9 | ? | |
| London | 3 | Radio | 01/01/2015 | 8 | ? |
| Leeds | 1 | 01/01/2015 | 10 | ? | |
| Leeds | 1 | Radio | 01/01/2015 | 8 | ? |
| Leeds | 2 | 01/01/2015 | 5 | ? | |
| Leeds | 2 | Radio | 01/01/2015 | 8 | ? |
| Leeds | 3 | 01/01/2015 | 9 | ? | |
| Leeds | 3 | Radio | 01/01/2015 | 8 | ? |
| London | 1 | 02/01/2015 | 9 | ? | |
| London | 1 | Radio | 02/01/2015 | 4 | ? |
| London | 2 | 02/01/2015 | 6 | ? | |
| London | 2 | Radio | 02/01/2015 | 7 | ? |
| London | 3 | 02/01/2015 | 2 | ? | |
| London | 3 | Radio | 02/01/2015 | 6 | ? |
| Leeds | 1 | 02/01/2015 | 9 | ? | |
| Leeds | 1 | Radio | 02/01/2015 | 4 | ? |
| Leeds | 2 | 02/01/2015 | 6 | ? | |
| Leeds | 2 | Radio | 02/01/2015 | 7 | ? |
| Leeds | 3 | 02/01/2015 | 2 | ? | |
| Leeds | 3 | Radio | 02/01/2015 | 6 | ? |
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...
Thanks Konstantinos. I will research your steps and pointers!