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.
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.
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 |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |