Difference between Consecutive Rows

[ihavepowerbi]

Hello everyone, I am new to the PBI and have been playing with some data. I have a table with fields; Products like A, B, C, etc., SubCategory like AA, AB, AC, etc., Date and Quantity which is cumulative for every combination of Product and Subcategory based on the Date. 

Product	SubCategory	Date	Qt
A	AA	4/1/2020	5
A	AA	4/2/2020	9
A	AA	4/3/2020	11
A	AA	4/4/2020	17
A	AA	4/5/2020	25
A	AB	4/1/2020	5
A	AB	4/2/2020	8
A	AB	4/3/2020	12
A	AB	4/4/2020	20
A	AB	4/5/2020	21
A	AC	4/1/2020	2
A	AC	4/2/2020	6
A	AC	4/3/2020	15

For each date, I want to calculate the difference between Qt on any date and Qt on the date before that date (We also need to take an account that they are cumulative sum). For example:

Date	Difference
4/1/2020	5+5+2 = 12
4/2/2020	(9+8+6)-(12)=11

I tried several ways but could not succed. Can anyone help me on this?

[Anonymous]

While having a date table is best pratice, this solution does not require one.

Measures are...

          Total_Qt = SUM(Product_Sales[Qt])

         QT_RT = //RT = Running Total
            VAR LastVisibleDate = MAX('Product_Sales'[Date])
           RETURN
          CALCULATE(
                  SUM( Product_Sales[Qt]),
                          FILTER(
                               ALL( 'Product_Sales'),
                              Product_Sales[Date] <= LastVisibleDate)
                       )

        Diff =
          VAR Curr_Date = SELECTEDVALUE('Product_Sales'[Date])
          VAR Prev_Date = Curr_Date - 1

          RETURN
             CALCULATE([QT_RT], DATESBETWEEN('Product_Sales'[Date], Prev_Date, Curr_Date ) )

          - CALCULATE([QT_RT], DATESBETWEEN('Product_Sales'[Date], Prev_Date, Prev_Date ) ) 

Let me know if this worked for you. Thanks

[Anonymous · Accepted solution]

In case you want it, here is a more complete solution using a Date table to get both the daily difference and the cumulative daily difference...

Date Table

Date =

VAR Dates =

    CALENDAR (

        DATE ( YEAR ( MIN ( 'Sales'[Date] ) ), 1, 1 ),

        DATE ( YEAR ( MAX ( 'Sales'[Date] ) ), 12, 31 )

    )

RETURN

    ADDCOLUMNS (

        Dates,

        "Year", YEAR ( [Date] ),

        "Quarter", FORMAT ( [Date], "q" ),

        "Month", MONTH ( [Date] ),

        "Month_Name", FORMAT ( [Date], "mmmm" ),

        "Month_Short_Name", FORMAT ( [Date], "mmm" ),

        "Year_Quarter", YEAR ( [Date] ) & "-Q"

            & FORMAT ( [Date], "q" ),

        "Year_Month",  YEAR ( [Date] ) & "-"

            & FORMAT ( [Date], "mm" )

    )

DatesWithsales Column

      DatesWithSales = 'Date'[Date] <= MAX( 'Sales'[Date])

Total_Date_Sales Measure

     Total_Date_Sales = SUMX ( RELATEDTABLE(Sales), [Total_Qt])

Sales Table Measures

Total_Qt = SUM(Sales[Qt])

Cum_Qt = // Running Total //

      VAR LastVisibleDate = MAX('Sales'[Date])

      RETURN

          CALCULATE(

              SUM( Sales[Qt]),

              FILTER(

                  ALL( 'Sales'),

                  Sales[Date] <= LastVisibleDate)

          )

Cum_Daily_Qt_Diff =

VAR Curr_Date =

    SELECTEDVALUE ( 'Date'[Date] )

VAR Prev_Date = Curr_Date - 1

RETURN

    CALCULATE ( [Cum_Qt], DATESBETWEEN ( 'Date'[Date], Prev_Date, Curr_Date ) )

        - CALCULATE ( [Cum_Qt], DATESBETWEEN ( 'Date'[Date], Prev_Date, Prev_Date ) )

Daily_Qt_Diff =

VAR Curr_Date =

    SELECTEDVALUE ( 'Date'[Date] )

VAR Prev_Date = Curr_Date - 1

RETURN

    CALCULATE ([Total_Date_Sales], DATESBETWEEN ( 'Date'[Date], Curr_Date, Curr_Date ) )

  - CALCULATE ( [Total_Qt], DATESBETWEEN ( 'Date'[Date], Prev_Date, Prev_Date ) )

[amitchandak]

@ihavepowerbi ,

Create measures like this with Date Calendar dimension

Total QT =sum(Table[QT])
last QT= CALCULATE([Total QT],Dateadd(Date[DAte],-1,Day))
Diff = [Total QT] -[last QT]

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/