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 team,
I have a 4-5-4 financial calendar where I have a different number of weeks each month. I am trying to compare monthly performance, and I cannot use the sum as each month has a different number of months, therefore I have calculated the weekly average of a month by dividing the monthly total by the number of weeks.
However, I am having a bit of trouble calculating Month on Month of that average value as I cannot use DATEADD. Looking for advice, many thanks in advance.
Hi @newyorker ,
The following DAX might work for you:
MonthlySum =
CALCULATE(SUM('Table'[Value]), ALLEXCEPT('Table', 'Table'[Month]))
AveragePerWeek =
[MonthlySum] / DISTINCTCOUNT('Table'[Week])
And the final output is shown in the following figure:
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello! I suggest adding in a date table (below I have a link to my blog post about a date reference table which has a link to the SQLBI one I love using - it accomodates regular calendar, fiscal, and fiscal weekly so perfectly handles 4-5-4/4-4-5 and makes doing time intelligence a breeze). If you do not have a full date field in your date you can join on that, if you do not you can create a date utilizing the fields you already have. Then....
To handle the average you want, use the AVERAGEX function like below:
Proud to be a Super User! | |
Hi @audreygerred this is exactly what I need, would you mind terribly sharing the PBIX? Many thanks in advance.
Hi! I didn't save the example I created, with your data from your screenshot, but if you're taking about the pbix that has the date table I love using, you can download that from here. https://www.sqlbi.com/tools/dax-date-template/
If you need any help setting it up or doing the join to your fact table let me know. Once you have it in place and joined you can create the two measures I provided.
Proud to be a Super User! | |
Hi @newyorker
Can you please share the raw data??
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 |
---|---|
105 | |
104 | |
87 | |
73 | |
66 |
User | Count |
---|---|
122 | |
112 | |
98 | |
79 | |
72 |