Calculate multiple time averages in one measure (YD, 7D, 28D, 84D, 7DLY)

[Anonymous]

Hello all,

I'm hoping someone can help me with this query. 

I'm trying to create a single measure with multiple date calculations and an output. 

The aim is to compare date periods for orders including Yesterday, Last 7 Days, Last 4 Weeks, Last 8 Weeks, Last 12 Weeks, Last 7 Days (LY).

I have a customer order table called orderCidOidShopify which houses order level data (dates, order_id, customer_id...). I also have supporting tables that groups those orders into filterable dimensions like 445 cal, gender, country etc.

I have been able to do the majority through seperate meausre like the one below:

7 Day Avg (order) = DIVIDE(CALCULATE (
        DISTINCTCOUNT( orderCidOidShopify[order_id] ),
        FILTER ( 'orderCidOidShopify', orderCidOidShopify[day] < max(orderCidOidShopify[day]) && orderCidOidShopify[day] >= max(orderCidOidShopify[day])-7 )
    ),7)

Which has outputted the following example:

However, if I want to do this for orders, revenue, traffic etc. I will end up with so many individual measures which isn't even considering if I wish to group into weekly or monthly period comparisons.

I'm hoping there is a way to create a single measure covering the date comps and also calculates the delta %. i would then have a meausre for each metric instead of a measure for each date comp and metric. The aim is to output like the following:

I would then have a sheet which has these tables in multiple metrics and even into small multiples. 

The main thing I am really struggling with is being able to do the calculation in a single measure.

Any help is appreciated.

[ValtteriN · Accepted solution]

Hi,

This seems like a perfect use case for a calculation group. Here is the documentation about the matter:
https://docs.microsoft.com/en-us/analysis-services/tabular-models/calculation-groups?view=asallprodu...

By creating the calaculations using SELECTEDMEASURE. You can get the output you described. 

Ping me with @ if you have questions about the topic.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[Anonymous]

Thank you. Helped a lot. Looks like PBI doesn't allow custom labels like in tableau but this has helped get the majority ticked off. Thanks