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.
Hi
I have the below data, and i need a custom column to calculate the previous days value based on the "product" and "NewOrExistingMember" filter. There are multiple Products and also multiple "NewOrExistingMember" selections
Any help would be much appreciated!
🙂
Solved! Go to Solution.
In DAX you can’t reference the previous row in any way because there is no order to the rows, So fi you need to get previous row, you can create a rank column, and then use LOOKUPVALUE function to get it.
Sample data and DAX for you reference.
Rank = RANKX ( FILTER ( Table1, Table1[Group] = EARLIER ( Table1[Group] ) && Table1[Group2] = EARLIER ( Table1[Group2] ) ), Table1[Amount], , ASC )
PreviousValue = LOOKUPVALUE ( Table1[Amount], Table1[Group], Table1[Group], Table1[Group2], Table1[Group2], Table1[Rank], Table1[Rank] - 1 )
Regards,
Charlie Liao
In DAX you can’t reference the previous row in any way because there is no order to the rows, So fi you need to get previous row, you can create a rank column, and then use LOOKUPVALUE function to get it.
Sample data and DAX for you reference.
Rank = RANKX ( FILTER ( Table1, Table1[Group] = EARLIER ( Table1[Group] ) && Table1[Group2] = EARLIER ( Table1[Group2] ) ), Table1[Amount], , ASC )
PreviousValue = LOOKUPVALUE ( Table1[Amount], Table1[Group], Table1[Group], Table1[Group2], Table1[Group2], Table1[Rank], Table1[Rank] - 1 )
Regards,
Charlie Liao
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |