Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I'm facing the following problem and I'm having trouble solving it. Given the following table
Date | Benchmark | Desired Column |
03/02/2021 | 119725 | 1,0126 |
02/02/2021 | 118234 | 1,0074 |
01/02/2021 | 117365 | 1,0117 |
29/01/2021 | 116007 | 0,9900 |
28/01/2021 | 117172 | 0,9892 |
27/01/2021 | 118443 | 0,9894 |
26/01/2021 | 119708 | 0,9920 |
22/01/2021 | 120673 | 1,0014 |
21/01/2021 | 120502 | 1 |
I want to calculate the "Desired Column" that is reached by dividing the N day by N-1 day. So for example, for the date 03/02/2021 the math would be 119725/118234 = 1,0126. Is there any DAX formula that could work in this problem? I have tried some combinations of formulas but no success. Thanks in advance
Here is a measure expression you can use in a table visual with your date and benchmark columns.
NewMeasure =
VAR vThisDate =
MIN ( Benchmark[Date] )
VAR vPrevDate =
CALCULATE (
MAX ( Benchmark[Date] ),
ALL ( Benchmark[Date] ),
Benchmark[Date] < vThisDate
)
VAR vThisBenchmark =
SUM ( Benchmark[Benchmark] )
VAR vPrevBenchmark =
CALCULATE (
SUM ( Benchmark[Benchmark] ),
ALL ( Benchmark[Date] ),
Benchmark[Date] = vPrevDate
)
VAR vDenominator =
IF (
ISBLANK ( vPrevBenchmark ),
vThisBenchmark,
vPrevBenchmark
)
RETURN
DIVIDE (
vThisBenchmark,
vDenominator
)
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
User | Count |
---|---|
57 | |
22 | |
21 | |
19 | |
16 |
User | Count |
---|---|
86 | |
84 | |
52 | |
37 | |
23 |