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.
Maybe I'm over-thinking this, maybe not ...
I'm pulling my data by month, and each month has the following # of weeks:
Jan 4 weeks
Feb 4 weeks
Mar 5 weeks
Apr 4 weeks
May 4 weeks
Jun 5 weeks
Jul 4 weeks
Aug 4 weeks
Sep 5 weeks
Oct 4 weeks
Nov 4 weeks
Dec 5 weeks
I want to create average sales per week measures, so in my dates table (called "Months") I added a column for the # of weeks by month. The dates table is linked to my fact table by month name and I pulled the # of weeks column into my fact table with the related function. Trouble is, the number of weeks was being summed for every store, product, etc.
After some trial and error, I found that the following measure accurately calculates the number of weeks for each month as well as the total # of weeks if I have multiple months in my view/filter. Is this the most efficient way to pull this or does anyone have an idea for a more efficient way to do this? (efficient in terms of memory processing)
Solved! Go to Solution.
Hi @Mainer04401 ,
We can use the following simplified formula to meet your requirement, we can use the performance analysis tools or dax studio to compare them:
Week Count =
SUMX (
VALUES ( Fact_Table[# of Weeks] ),
CALCULATE (
SUM ( Months[# of Weeks] )
)
)
Best regards,
Hi @Mainer04401 ,
We can use the following simplified formula to meet your requirement, we can use the performance analysis tools or dax studio to compare them:
Week Count =
SUMX (
VALUES ( Fact_Table[# of Weeks] ),
CALCULATE (
SUM ( Months[# of Weeks] )
)
)
Best regards,
honestly you should probably be using a calendar table. that would make working with dates and timeline slicers much easier and more versatile. If you only have monthly date you can still do this. You would just divide the month sales by the number of days then in your visuals just select the date hierarchy and set average totals instead of sum totals in your matrices and tables. The way you are doing it is fine.
Some other ideas to play with that may prove beneficial:
WEEKNUM: https://docs.microsoft.com/en-us/dax/weeknum-function-dax
Week Intelligence Artical : https://www.sqlbi.com/articles/week-based-time-intelligence-in-dax/
Week over Week calculations: https://community.powerbi.com/t5/Desktop/This-week-in-Dax/td-p/211393
Using WEEKNUM: https://community.powerbi.com/t5/Desktop/How-to-show-week-number-per-month/td-p/83607
Working with Weeks: https://insightsoftware.com/blog/working-with-weeks-in-power-bi/
Possible easier way to get week of month number: https://stackoverflow.com/questions/50140585/powerbi-convert-date-to-the-weeknumber-of-the-month
If this helps please kudo.
If this solves your problem please accept it as a solution.
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 |
---|---|
114 | |
98 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |