Calculate Difference Between Latest Date and Previous Date

[jpt1228]

Hello, this is my first post - I am typically a methodical searcher and can find most answers. This one I am stuck on.

I need to calculate the number difference between the most recent date and the one that is just previous. For example: The value of April 16, 2017 minus March 19, 2017 = 135.

There will always be a rolling 13 time periods. I do have a date table but all the examples I have found don't work when there are 2 dates in the same month. I get a mis-calculation such as in November 27, 2016 it shows the difference is -8,450 becasse it is summing both entried in October. I think I need to index the dates and then take the most recent date and calculate the difference to the next closest index date but I can't figure it out.

[Phil_Seamark · Accepted solution]

Hi @jpt1228

I used your screenshot and created a table in my PBIX file that I called Table2

Then I added two calculated columns.  The first adds an Index to the table as follows

Index = CALCULATE(
                COUNTROWS('Table2'), 
                FILTER(
                    ALL('Table2'), 
                    Table2[Date] < EARLIER('Table2'[Date])
                    )
                   ) + 1

Then with the Index column added, I could create the following calculated column to derive the difference between each row and it's prior row.

Value Change = 
    Table2[Values] - CALCULATE(
                            SUM('Table2'[Values]),
                            FILTER(
                                ALL(Table2),
                                Table2[Index] = EARLIER('Table2'[Index])-1)
                                )

This gave me the following table

It should work on your data as you describe it.  It will struggle if you have two rows with the same date.

I hope that helps

[Anonymous]

Hey @Phil_Seamark, just to piggy back of this topic since its fresh in your mind.  Would achieving the same results using a measure, making use of Calculate & LastNonBlank be possible?  Would this be a better or worse result, performance wise?

EDIT:  I should mention i was referring to your first solution.

[Phil_Seamark]

Hi @Anonymous

I think so.  The trade of is recalculating after every change (filter selection) which adds CPU time which may be handy if that is what is required, Otherwise if the index should always be the same then it's better off being a column and only calculated at dataload time.

[jpt1228]

We have been trying to figure this out. He provided a solution to summarize dates but I need to summarize geographies by time. The issue seems to be that the data is published every 4 weeks which can be 2x the same month throughout the year. Calculating the difference from the previous month works 10 out of 12 periods. The data I provided was a summary of the raw data. I am looking to visualize geographies in rows, dates in columns, and TDP numbers by month (time). Need to calculate difference from current time vs precious time even thoughost it might fall in the same month.

[Anonymous]

Hi @jpt1228

The calculated column provided by @Phil_Seamark should also work for you.

The only thing you may have to look at is calculating the index.  For this you could use edit query mode of your table.

Then sort the column geography ( customer) ascending and then sort the Tim (periods) ascending.

Now create the Index column again in query editor.

Lastly use the calculted column expression given by @Phil_Seamark and it will work.

Cheers

CheenuSing

[jpt1228]

Time	Geography	Value
April 16 2017	Customer 1	384
April 16 2017	Customer 2	362
April 16 2017	Customer 3	342
April 16 2017	Customer 4	600
March 19, 2017	Customer 1	15
March 19, 2017	Customer 2	42
March 19, 2017	Customer 4	69
March 19, 2017	Customer 3	26

Hi Phil, I tried to build the formula but it was not working. I was sharing summarized data for the example but in actuality the source data is structured like above. There is an entry for each date for customer and then the value. I think the index was not working because there are many dates but then the customer and the value changes. This is a flat file built from the 3 columns that I need to convert to a time series with the difference between the two time periods by customer. The table goes on with more dates, customers and values.

[Phil_Seamark]

Hi @jpt1228

You could try and create a calculated table as step 1 as follows.  Just replace where I have Table3 with your table name.

Then add the index column to this table and then the difference column.

 SummarizedTable =SUMMARIZE('Table3',[Time],"Sum of Value" , SUM('Table3'[Value]))

[jpt1228]

Hi Phil, I am sure the solution is right under my nose. I should clarify I posted a screen shot of the report in PowerBI not the data table if that matters.

I have created the 2 calculated columns you suggested in the TDP DATA table

Here are the fields in my model:

 

SummarizedTable =SUMMARIZE('Table3',[Time],"Sum of Value" , SUM('Table3'[Value]))

 This is how I interpret your SummarizedTable formula Insert New Measure and then SummarizedTable = SUMMARIZE('TDP DATA',[WhatIsTime?], Not sure what goes here, sum('TDP DATA'[TDP]))

Lastly if I am building a visuilization of this data my formula does not like the multiple same months. Bringing in the Value change field you gave me is bringing in the TDP number and not the difference between previous time period.

[Phil_Seamark]

Hi @jpt1228

I think we are pretty close too.  Any chance you can post a small sample of your original table (pre-summerized) so that I can tailor the code for the calculated table to suit.  You can PM it to me if you prefer.