Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |