DAX: Subtract previous row from current row

[markus_zhang]

Hi Experts,

This must be done in a measure, not in a calculated column. I'm working in Excel 2016 not Power BI.

I have a sales table qryDump with the following content: (Note there is gap between dates and the Impressions reset at the beginning of each month)

Date | ID | Impressions |

0903 | 44 | 100

0903 | 101 | 50

0903 | 993 | 2000

0905 | 44 | 300

0905 | 101 | 250

0905 | 993 | 5000

0906 | 44 | 1000

0906 | 101 | 500

0906 | 993 | 20000

1002 | 44   | 50

1002 | 101 | 100

 1002 | 993 | 200

Other info:

I also have a date table tbDate listing all of the available dates (same as the first column of my sales table), but with unique value so I can drop it on a pivot table.

Date

0903

0905

0906

1002

And another table tbSpot listing unique IDs (just 44, 101 and 993)

​Pivot Table setup:

tbSpot[ID] and tbDate[Date] in ROW and the qryDump[Impression] I'm trying to get in VALUE.

I filtered ID to 44 and here is what Pivot Table is showing:

44 - 0903 - 100

     - 0905 - 300

     - 0906 - 1000

     - 1002 - 50

Question: I'd like to get daily Impression instead of cumulative impression in the Pivot Table

44 - 0903 - 100

     - 0905 - 200

     - 0906 - 700

     - 1002 - 50

My code

I decided to ignore the reset problem and tried to create a measure that shifts the data in Pivot Table, basically I want to achieve this first:

44 - 0903 - 100 - 200

     - 0905 - 200 - 700

     - 0906 - 700 - 50

     - 1002 - 50

Then I can just do a substraction and get: (ignore resetting)

44 - 0903 - 100 - 200 - 100

     - 0905 - 200 - 700 - 500

     - 0906 - 700 - 50

     - 1002 - 50

Here is the code that only shows 0 for each Pivot Table row:

=	VAR curRow = FIRSTNONBLANK(tbDate[Date],1)
	RETURN
	CALCULATE(
		FIRSTNONBLANK(qryDump[RK_Impressions], 1),
		FILTER(ALL(tbDate), tbDate[Date] > curRow))

[askpbiuser]

Hi @Greg_Deckler  -
I have a little similar question and would you be able to help -
I have a requirment  to subtract from previous row, but the sum of that category, something like ,

I need to create Calc which is basically the MAX(Sum of values - sales value for that category, date row - previous calculated result.)
Please let me know if you have any questions.

[Greg_Deckler]

@askpbiuser Start a new thread for this question. Post sample data as text and expected output. Tag me in the post.

[askpbiuser]

Thank you.
Here is the separate thread - https://community.fabric.microsoft.com/t5/Desktop/DAX-Previous-value-from-measure/td-p/3880390 

[v-yulgu-msft]

Hi @markus_zhang,

Please refer to below measure:

Result =
VAR currentindex =
    COUNTROWS (
        FILTER (
            ALLSELECTED ( Impressions[Date] ),
            MONTH ( Impressions[Date] ) = MONTH ( MAX ( Impressions[Date] ) )
                && Impressions[Date] <= MAX ( Impressions[Date] )
        )
    )
VAR previouImpressions =
    CALCULATE (
        SUM ( Impressions[Impressions] ),
        FILTER (
            ALLSELECTED ( Impressions ),
            [Index]
                = currentindex - 1
                && MONTH ( Impressions[Date] ) = MONTH ( MAX ( Impressions[Date] ) )
        )
    )
RETURN
    SUM ( Impressions[Impressions] ) - previouImpressions

Best regards,

Yuliana Gu

[Anonymous]

https://community.powerbi.com/t5/Desktop/reference-Previous-row-using-measures/m-p/725894#M350298

help me out on the same issue.

[markus_zhang]

Thanks, this is basically everything in a measure and impressive! I'll see what I can learn from your code.

[Greg_Deckler]

There may be a way to do it without it, but I would add an Index in your query. Then it should be pretty trivial using EARLIER.

[markus_zhang]

Thanks Greg, did you mean that I should add an additional column with 1, 2, 3... for the tlDate table?

I'm not sure if I can use EARLIER() in measures though, might need a double loop.

[Greg_Deckler · Accepted solution]

Oh yeah, should have explained that. Yes you can use EARLIER in measures, you would do something like this:

Measure = 
VAR __index = MAX([Index]) //in visual table context, this is the current index
VAR __table = 'Table'
VAR __table1 = ADDCOLUMNS(__table,"__diff",[Impressions] - MAXX(FILTER(ALL('Table'),[Index]=EARLIER([Index])-1),[Impressions])
RETURN
MAXX(FILTER(__table1,[Index]=__index),[__diff])

Something like that, I didn't test it for syntax erros.

[Anonymous]

https://community.powerbi.com/t5/Desktop/reference-Previous-row-using-measures/m-p/725894#M350298

Please help me out in this previous row logic.

---

@Greg_Deckler wrote:

Oh yeah, should have explained that. Yes you can use EARLIER in measures, you would do something like this:

Measure = 
VAR __index = MAX([Index]) //in visual table context, this is the current index
VAR __table = 'Table'
VAR __table1 = ADDCOLUMNS(__table,"__diff",[Impressions] - MAXX(FILTER(ALL('Table'),[Index]=EARLIER([Index])-1),[Impressions])
RETURN
MAXX(FILTER(__table1,[Index]=__index),[__diff])

Something like that, I didn't test it for syntax erros.

---