Forum Discussion
Kudo CollectorNeed help creating date delta columns
you may also take a look at lookupvalue() function ...
Memorable MemberHope 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
Kudo CollectorI 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 | ? |