Forum Discussion
Need help optimising / simplifying formula and additional filtering
Hi dpc_development ,
You may change the formula above like DAX below.
cr3 =
VAR tbl =
ALLSELECTED ( 'CMC Daily Return' )
VAR onDate =
MAX ( 'CMC Daily Return'[last_updated] )
VAR fk_count =
DISTINCTCOUNT ( 'CMC Daily Return'[fk] )
VAR fk_item =
MAX ( 'CMC Daily Return'[fk] )
VAR list_item =
MAX ( 'CMC Daily Return'[list_id] )
VAR fromDate =
CALCULATE (
MIN ( 'CMC Daily Return'[last_updated] ),
FILTER (
tbl,
'CMC Daily Return'[fk] = fk_item
&& 'CMC Daily Return'[list_id] = list_item
)
)
VAR d =
IF (
'CMC Daily Return'[last_updated] = fromDate,
1,
1 + 'CMC Daily Return'[daily_return]
)
VAR _return =
CALCULATE (
PRODUCTX ( 'CMC Daily Return', d ),
FILTER (
tbl,
'CMC Daily Return'[fk] = fk_item
&& 'CMC Daily Return'[list_id] = list_item
&& 'CMC Daily Return'[last_updated] >= fromDate
&& 'CMC Daily Return'[last_updated] <= onDate
)
) * 'CMC Daily Return'[ratio]
RETURN
SUMX ( 'CMC Daily Return', _return ) / fk_count
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-xicaiThat will not work, since I have multiple list_items under each fk_item. Hence, putting the assignment of list_item and minDate at the beginning will only make the formula calculate the values for one list_item.
I have extracted the dataset and formula into a separate modeling file, but was not sure how to attach it here.
Hi dpc_development ,
I am not sure what desired result would you want, could you please share your sample data and desired output screenshots for further analysis? You can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.
Please read this post to get your answer quickly: How to Get Your Question Answered Quickly.
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.