Forum Discussion

warrencowan's avatar
warrencowan
Kudo Collector
11 years ago
Solved

Need help creating date delta columns

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 b...
  • andre's avatar
    andre
    11 years ago

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

warrencowan's avatar
warrencowan
Kudo Collector
11 years ago
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's avatar
konstantinos
Memorable Member
11 years ago

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 

 

 

Untitled.png