DAX dynamic rownumber and lag partition by

[Anonymous]

Hello, could you please help me with PBI DAX formulas:

I have such data which I pulled out from SQL database using rownumber() and lag() partition by function

ROW_NUMBER() OVER(PARTITION BY outlet, product ORDER BY outlet, product, date ASC) AS rownumber,
LAG(date, 1, 0) OVER(PARTITION BY outlet, product ORDER BY outlet, product, date ASC) AS prevdate

period	outlet	product	date	amount	rownumber	prevdate	diff between dates
2018P08	25319005	1000089	8/3/2018	3	1	1/1/1900	0
2018P09	25319005	1000089	8/17/2018	10	2	8/3/2018	13
2018P09	25319005	1000089	9/7/2018	4	3	8/17/2018	22
2018P10	25319005	1000089	9/28/2018	5	4	9/7/2018	20

And now I want to use this data in Power BI, but rownumber and prevdate columns is static. When I use filters for example date > 8/3/2018 rownumber starts from 2. For example, after filtering I want table be like the following:

period	outlet	product	date	amount	rownumber	prevdate	diff between dates
2018P09	25319005	1000089	8/17/2018	10	1	1/1/1900	0
2018P09	25319005	1000089	9/7/2018	4	2	8/17/2018	22
2018P10	25319005	1000089	9/28/2018	5	3	9/7/2018	20

How to reach it using DAX in Power BI?

[v-lili6-msft]

hi, @Anonymous

If you want to get a dynamic result, you need to create a measure.

For ROW_NUMBER() OVER(PARTITION BY outlet, product ORDER BY outlet, product, date ASC) AS rownumber

You could use RANKX Function to create a measure:

RANK = RANKX(FILTER(ALLSELECTED('Table'),'Table'[outlet]=SELECTEDVALUE('Table'[outlet])&&'Table'[product]=SELECTEDVALUE('Table'[product])),CALCULATE(SELECTEDVALUE('Table'[date])),,ASC)

But for LAG(date, 1, 0) OVER(PARTITION BY outlet, product ORDER BY outlet, product, date ASC) AS prevdate

there is no function can be used directly, and It is a little difficult to achieve. So let's have a try by the logic above it.

Best Regards,

Lin