Get calculated measure for a particular dimension only

[dpc_development]

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)

[v-frfei-msft · Accepted 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

[dpc_development]

@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.