Calculating # of Weeks in 4 or 5 Week Months

[Mainer04401]

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)

Week Count = sumx(summarize(Fact_Table,

Fact_Table[# of Weeks], -- this is pulled from my "Months" table w/ RELATED calculated column --

"@WEEKS",calculate(

    sum(Months[# of Weeks]))),[@WEEKS])

[meeranairws]

Hey! It sounds like you’re on the right track with your measure. I’ve run into similar issues when trying to calculate average sales per week, especially when dealing with 5 week months like March or December. Your approach seems efficient, but you might also want to look into using a DAX formula that directly pulls the number of weeks from your "Months" table without needing to summarize it across multiple stores/products. That could help with memory processing, especially when looking ahead to 5 week months in 2024 and beyond.

[v-lid-msft · Accepted 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,

[Tad17]

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.