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.
Hi there,
I was wondering how I can easily do a percent change across yesterday's data vs the average of the same day for the 4 weeks prior. This will be for multiple metrics so would I have to create a measure for each metric or is there some automated way to have it work for each metric.
Here's what I'd like to acheive
sample data
Yesterday | Same day 1 week ago | Same day 2 weeks ago | Same day 3 weeks ago | Same day 4 weeks ago | Average of Same day 1 to 4 weeks ago | % change of Yesterday vs Avg | |
Visitors | 100 | 101 | 111 | 123 | 124 | 115 | -13% |
Quote Starts | 121 | 121 | 123 | 134 | 123 | 125 | -3% |
I would like to then visualize the data in either a key metrics card with the % change below in the same card or in a table as a column header next to Yesterday's data.
@jnrezk , Try measures like below with help from date table
example
Day Behind yesterday = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,DAY))
7 Days Behind yesterday = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-8,DAY))
14 Days Behind yesterday = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-15,DAY))
21 Days Behind yesterday = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-22,DAY))
28 Days Behind yesterday = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-29,DAY))
refer if needed
Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8
Day Intelligence - Last day, last non continous day
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c324...
Hi, I am trying to avoid a ton of calculations as I have to do this for like 10 metrics and then i have to make at least 5 calculations per metric? Is there a simpler way that could work for all the metrics together? or at least can i make the average calculation and percent change calculation in one measure so there's less to make.
Hi. @jnrezk
You can try calculation group to reduce redundant measures created by grouping standard measure expressions.
https://apexinsights.net/blog/10-uses-for-calculation-groups
leveraging-calculation-groups-power-bi
Best Regards,
Community Support Team _ Eason
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |