locked
Calculated column with difference from previous values? RRS feed

  • Question

  • I have a table with three columns: date, campaign, and clicks. Every ten minutes a row was added indicating how many clicks a campaign has.

    My goal is to figure out how many clicks were made in each 10-minute period. I'd like to make a column whose value for a given row is the difference between clicks in that row and clicks in the previous row (columns are sorted by date). This is similar to Excel's 'Difference From..." (previous) options. What would be the formula or functions used to make a calculated column for this?

    Thank you for the help.

    Monday, March 25, 2013 7:39 AM

Answers

  • Nice work getting the formula working to return the previous date - its a more elegant solution that mine! The beauty of DAX is than once you've got a few patterns in your locker it starts to become much easier!

    Conceptually it may make more sense to swap the order of the final part around so its:

    Table1[Date Time]<EARLIER(Table1[Date Time])

    i.e. Where the [Date Time] column value is less than the [Date Time] value in the current row. For me EARLIER() is badly named for the Excel crowd and should have been called something like CURRENTROWVALUE(). Either way its the same thing and produces the same answer.

    I called that column [Previous Date Time] and then wrote this measure to return the total clicks from the Previous Date Time:

    =CALCULATE(
               sum(Table1[Total Clicks]),
               ALL(Table1),
               Table1[Date Time]=EARLIER(Table1[Previous Date Time]),
               Table1[Campaign]=EARLIER(Table1[Campaign])
               )

    It was then a simple of case of subtracting this number from the current total to give the clicks in the time since the last entry.

    I uploaded my workings on SkyDrive.

    Jacob


    Monday, March 25, 2013 10:58 PM

All replies

  • Michael, I've assumed that your data look as the picture below whereby total clicks for the campaign are recorded every 10 minutes (hence the need to calculate the difference) and the 10 minute interval is precise (although could be adjusted if not):

    The measure I wrote looks like this:

    =[Total Clicks]-
         CALCULATE(
                   SUM(Table1[Total Clicks]),
                   ALL(Table1),
                   Table1[Date Time]=EARLIER(Table1[Date Time])-(1/(24*6)),
                   Table1[Campaign]=EARLIER(Table1[Campaign])
                   )
     

    Basically it opens out the table context and then finds the same campaign and the date time 10 minutes previous. I uploaded my 2 minute workbook here.

    HTH

    Jacob


    Monday, March 25, 2013 9:26 AM
  • Thanks so much for the answer, Jacob. This has been a great jumping-off point for me.

    It turns out, my data was not taken consistently every 10 minutes. Sometimes it's 8, sometimes it's 12 minutes apart. To remedy this, I want to create a column called 'previousDate', which contains the date of the last entry from that campaign. I put it in its own column because I'll be using it a lot and I don't want to recalculate it every time.

    Here's how I made previousDate column:

    =CALCULATE(
    	MAX(Table1[Date Time]),
    	All(Table1),
    	Table1[Campaign]=EARLIER(Table1[Campaign]),
    	EARLIER(Table1[Date Time])>Table1[Date Time]
    	)

    (not sure why EARLIER(reference[date] is GREATER than reference[date], but it works)

    Now, how can I reference rows using the new column to get 'Total Clicks' values?

    Thanks again for helping.




    Monday, March 25, 2013 7:30 PM
  • Nice work getting the formula working to return the previous date - its a more elegant solution that mine! The beauty of DAX is than once you've got a few patterns in your locker it starts to become much easier!

    Conceptually it may make more sense to swap the order of the final part around so its:

    Table1[Date Time]<EARLIER(Table1[Date Time])

    i.e. Where the [Date Time] column value is less than the [Date Time] value in the current row. For me EARLIER() is badly named for the Excel crowd and should have been called something like CURRENTROWVALUE(). Either way its the same thing and produces the same answer.

    I called that column [Previous Date Time] and then wrote this measure to return the total clicks from the Previous Date Time:

    =CALCULATE(
               sum(Table1[Total Clicks]),
               ALL(Table1),
               Table1[Date Time]=EARLIER(Table1[Previous Date Time]),
               Table1[Campaign]=EARLIER(Table1[Campaign])
               )

    It was then a simple of case of subtracting this number from the current total to give the clicks in the time since the last entry.

    I uploaded my workings on SkyDrive.

    Jacob


    Monday, March 25, 2013 10:58 PM
  • Great! It works almost perfectly! The only problem is the column's values for the first entries that don't have previous rows. For these, it displays their [Total Clicks] value -- I'd like them to display 0. I'm guessing there's some conditional way to do this, maybe like "if [Previous Date Time] is empty, then =0" ?
    Tuesday, March 26, 2013 12:00 AM
  • I'm not sure it will be an issue with your real data because in reality the first instance of capture would surely have zeros?

    Anyway the syntax would be = IF ( [Previous Date Time] = blank(), 0, etc......

    Jacob

    Tuesday, March 26, 2013 12:48 AM