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.
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
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 |
---|---|
49 | |
26 | |
21 | |
16 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |