Forum Discussion
Need help creating date delta columns
you may also take a look at lookupvalue() function ...
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
Thanks Konstantinos, thats a very helpful and detailed example. I will give it a shot and report back
you may also take a look at lookupvalue() function ...
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?
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?