Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
ihavepowerbi
New Member

Difference between Consecutive Rows

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. 

 
 
ProductSubCategoryDateQt
AAA4/1/20205
AAA4/2/20209
AAA4/3/202011
AAA4/4/202017
AAA4/5/202025
AAB4/1/20205
AAB4/2/20208
AAB4/3/202012
AAB4/4/202020
AAB4/5/202021
AAC4/1/20202
AAC4/2/20206
AAC4/3/202015

 

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:

DateDifference
4/1/20205+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?

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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 ) )

 

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

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

Prev  Day Diff.jpg

Anonymous
Not applicable

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
Super User
Super User

@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/

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.