Joshua Chartier on 12 Jun 2020 03:37:37
Currently there is no way to express ordered iteration in DAX. This makes it impossible (or practically impossible) to calculate values where order matters. For example, I would like to be able to use DAX to calculate a final price after a set of discounts. These discounts can either be fixed dollar discounts or % of the previous price but the order in which they are applied matters and is not uniform.
Case 1:
Original Price: $10
1. Discount: 50%
2. Discount: $2
Final Price: (10 * .5) - 2 = $3
Case 2:
Original Price: $10
1. Discount: $2
2. Discount: 50%
Final Price: (10 - 2) * .5 = $4
My data source supplies the order of the discounts, but there could be arbitrarily many of them and no set order between $ and % discounts.
To solve this I propose to add the following to DAX:
SORT(
- Comments (1)
RE: Ordered iterators
PREVIOUS could also take an optional column name which would allow for quick shifting data.
Here are a list of ideas that could also benefit from this proposal:
https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/38684635-dax-cumulative-total-by-previous-result-same-colu
https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/37699576-add-table-calculation-dax-functions
https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/37234996-running-total-for-declared-date-range
https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/40079857-dax-function-to-create-column-with-shifted-rows