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 have a dataset with a field called 'cr'. It is basically the following iterative product formula...
Today's CR = Yesterday's CR * (1 + rate)
The requirement is to graph the 'cr' value, but to restart it from '1' in the visual, even after the filters are changed. So I have two calculated measures...
1. first_cr, which is basically the first value in the filtered time range
first_cr = VAR list = SELECTEDVALUE('CMC Daily Return'[fk]) VAR min_updated = CALCULATE(MIN('CMC Daily Return'[last_updated]), 'CMC Daily Return'[fk] = list, ALLSELECTED('CMC Daily Return')) RETURN CALCULATE( SELECTEDVALUE('CMC Daily Return'[cr]), 'CMC Daily Return'[last_updated] = min_updated, ALLSELECTED('CMC Daily Return'[fk]) )
2. The cumulative return formula starting from 1
cumulative_return = IF([first_cr]>0, AVERAGE('CMC Daily Return'[cr])/[first_cr])
Firstly, is my first_cr formula the most efficient formula possible to get the first 'cr' value within a filtered time range of the dataset?
Secondly, I now need to find the 'cumulative return' of a particular dimension to use it as a benchmark in other visualisations or formula. I tried using the above calculated measures in various methods, but either there were no values or the visualisation did not respect the date filter due to some reason.
Eventually, I was able to get the following formula working, which is essentially recalculating everything. Can the above and below formulas be simplified further?
item1_return = VAR min_updated = CALCULATE(MIN('CMC Daily Return'[last_updated]), ALLSELECTED('CMC Daily Return')) var onDate = MAX('CMC Daily Return'[last_updated]) var item1_first_cr = CALCULATE( [first_cr], 'CMC Daily Return'[last_updated] = min_updated, 'CMC Daily Return'[fk] = "item1", ALLSELECTED('CMC Daily Return') ) var item1_cr = CALCULATE( AVERAGE('CMC Daily Return'[cr]), 'CMC Daily Return'[last_updated] = onDate, 'CMC Daily Return'[fk] = "item1", ALLSELECTED('CMC Daily Return') ) RETURN IF(item1_first_cr > 0, item1_cr / item1_first_cr)
Solved! Go to Solution.
Hi @dpc_development ,
The formula should get the same result as yours.
item1_return = var onDate = MAX('CMC Daily Return'[last_updated]) var item1_first_cr = CALCULATE( [first_cr], 'CMC Daily Return'[fk] = "item1", ALLSELECTED('CMC Daily Return') ) var item1_cr = CALCULATE( AVERAGE('CMC Daily Return'[cr]), 'CMC Daily Return'[last_updated] = onDate, 'CMC Daily Return'[fk] = "item1", ALLSELECTED('CMC Daily Return') ) RETURN IF(item1_first_cr > 0, item1_cr / item1_first_cr)
Regards,
Frank
Hi @dpc_development ,
The formula should get the same result as yours.
item1_return = var onDate = MAX('CMC Daily Return'[last_updated]) var item1_first_cr = CALCULATE( [first_cr], 'CMC Daily Return'[fk] = "item1", ALLSELECTED('CMC Daily Return') ) var item1_cr = CALCULATE( AVERAGE('CMC Daily Return'[cr]), 'CMC Daily Return'[last_updated] = onDate, 'CMC Daily Return'[fk] = "item1", ALLSELECTED('CMC Daily Return') ) RETURN IF(item1_first_cr > 0, item1_cr / item1_first_cr)
Regards,
Frank
@v-frfei-msft I guess I didn't need the additional min_updated.
I'd also appreciate a comment on whether my first_cr formula or the method I am using to calculate a metric as percent of the first/earliest metric value in a date range is the best that it can be, or is a more efficient formula possible.
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 |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
108 | |
90 | |
64 |