Forum Discussion
Advocate IIIDAX: Subtract previous row from current row
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))
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.
10 Replies
- Greg_Deckler
Community Champion
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_zhangThanks 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
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.
- v-yulgu-msft
Microsoft Employee
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] ) - previouImpressionsBest regards,
Yuliana Gu
- markus_zhang
Thanks, this is basically everything in a measure and impressive! I'll see what I can learn from your code.
https://community.powerbi.com/t5/Desktop/reference-Previous-row-using-measures/m-p/725894#M350298
help me out on the same issue.
askpbiuserHelper I
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
