Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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/
Proud to be a Super User!
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/
Proud to be a Super User!
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