How to calculate last row-previous row difference in matrix based on date

[Sania]

hi all,

I have matrix as below where I can drill down data based on year/qurter/month/dates. Now I want to have a row in this matrix which calculates last row-previous row value.

Today as its 17th feb 2023, matrix will have data till 16th feb 2023. 

So today on 17th feb 2023 = 16th data -15th data (want to calculate difference data of 16-data of 15 and display it)

on 18th = 17th data-16th data.
on 19th=18th-17th data

and so on.... So its based on current row - previous row

Thankyou in advance for help

[Sania]

• People please kindly share it or add relevant tags , hashes so that the post can have a better reach. I am new to this community and not sure how to maximize the reach of post to get it to take it to the wider audience to help ke on it.

[grantsamborn]

Hi @Sania 

Would something like this help?

v1_Diff = 
VAR _CurrDt = SELECTEDVALUE( 'DataTable'[date] )
VAR _PrevDt = 
    MAXX(
        FILTER(
            ALL( 'DataTable'[date] ),
            'DataTable'[date] < _CurrDt
        ),
        'DataTable'[date]
    )
VAR _Curr_v1 = 
    CALCULATE(
        SUM( 'DataTable'[v1] ),
        'DataTable'[date] = _CurrDt
    )
VAR _Prev_v1 = 
    CALCULATE(
        SUM( 'DataTable'[v1] ),
        'DataTable'[date] = _PrevDt
    )
VAR _Result = _Curr_v1 - _Prev_v1
RETURN
    _Result

pbix: Previous_Values.pbix

[grantsamborn]

Hi @Sania 

After re-reading your question, I noticed I forgot to offset the dates by 1.

The first variable defined should be:

VAR _CurrDt = SELECTEDVALUE( 'DataTable'[date] ) - 1

pbix: Previous_Values.pbix

[Sania]

Thankyou @grantsamborn @, will try the given solution.

[Sania]

Hi Olgad,

I would like to provide some sample data.

Here the last date is 16th, prev day is 15th,so i want difference in values as below -

date	v1	v2	v3	v4
2/1/2023	98	65	100	10
2/2/2023	99	45	200	20
2/3/2023	100	23	300	30
2/4/2023	101	10	400	40
2/5/2023	54	67	500	50
2/6/2023	56	58	600	60
2/7/2023	7	48	700	70
2/8/2023	8	39	800	80
2/9/2023	25	29	900	90
2/10/2023	9	1	1000	100
2/11/2023	98	90	2000	101
2/12/2023	76	78	3000	102
2/13/2023	54	67	4000	103
2/14/2023	32	4556	5000	104
2/15/2023	21	34	6000	105
2/16/2023	12	23	7000	106
Difference b/w last row and previous row	-9	-11	1000	1

now if I take tomorrow , I would get 17th data , so i want to subtract 17th -16th data values

date	v1	v2	v3	v4
2/1/2023	98	65	100	10
2/2/2023	99	45	200	20
2/3/2023	100	23	300	30
2/4/2023	101	10	400	40
2/5/2023	54	67	500	50
2/6/2023	56	58	600	60
2/7/2023	7	48	700	70
2/8/2023	8	39	800	80
2/9/2023	25	29	900	90
2/10/2023	9	1	1000	100
2/11/2023	98	90	2000	101
2/12/2023	76	78	3000	102
2/13/2023	54	67	4000	103
2/14/2023	32	4556	5000	104
2/15/2023	21	34	6000	105
2/16/2023	12	23	7000	106
2/17/2023	76	78	3000	102
Difference b/w last row and previous row	64	55	-4000	-4

If not exact any thing that can achieve me to do this would be helpful. I provided simple data, but my actual data goes at hierarchical level. Sample snippet -

Thankyou in advance.

[olgad]

Hi, if you are on a new Power BI version (after Dec 2022), then window functions.

Please refer to the article with a step by step how to do it using offset function.

https://www.linkedin.com/pulse/can-window-functions-do-heavy-lifting-hr-reports-olga-dontsova/?track...

Let me know if you have any questions