cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dswinden
Frequent Visitor

Convert Weekly Sales to Daily Average Sales, and then Monthly Sales

Hello,

 

I have a data source that reports sales in a weekly sales format. I want to convert this weekly sales into a monthly sales format.  My thought process was to take

 

(Total Weekly Sales / Working Days in that week) = Daily Sales

 

And then once I have a daily sales measure, i can simply slice by month.

 

So lets say the week starting Monday May 30 has 143 units of sales.  Since there would be 5 working days that week, there would be 28.6 units sold per day.  That week would then have 57.2 units sold in May, and 85.8 units that were sold in June (May 30/31 = May, June 1/2/3 = June)

 

Has anyone had similar challenge before?

 

Week StartSales Units
May 23127
May 30143
June 6128
June 13100
1 ACCEPTED SOLUTION
v-polly-msft
Community Support
Community Support

Hi @dswinden ,

Please refer to my pbix file to see if it helps you.

Create another date table.

date _table = CALENDAR(DATE(2022,1,1),DATE(2022,12,31))

Then create some basic columns.

monthcolumn = MONTH('date _table'[Date])
weekday = WEEKDAY('date _table'[Date],2)
weeknum = WEEKNUM('date _table'[Date],2)
weekno. = WEEKNUM('Table'[Week Start],2)

Then create measures.

average_ =
CALCULATE (
    SUM ( 'Table'[Sales Units] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[weekno.] = SELECTEDVALUE ( 'date _table'[weeknum] )
    )
) / 5
count_days =
CALCULATE (
    COUNT ( 'date _table'[monthcolumn] ),
    FILTER (
        ALL ( 'date _table' ),
        'date _table'[weeknum] = SELECTEDVALUE ( 'date _table'[weeknum] )
            && 'date _table'[monthcolumn] = SELECTEDVALUE ( 'date _table'[monthcolumn] )
            && 'date _table'[weekday] <= 5
    )
)
result =
MAXX (
    FILTER (
        ALL ( 'date _table' ),
        [count_days] <> 5
            && 'date _table'[monthcolumn] = SELECTEDVALUE ( 'date _table'[monthcolumn] )
    ),
    'date _table'[average_] * [count_days]
)

vpollymsft_0-1656573527757.png

Best Regards

Community Support Team _ Polly

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-polly-msft
Community Support
Community Support

Hi @dswinden ,

Please refer to my pbix file to see if it helps you.

Create another date table.

date _table = CALENDAR(DATE(2022,1,1),DATE(2022,12,31))

Then create some basic columns.

monthcolumn = MONTH('date _table'[Date])
weekday = WEEKDAY('date _table'[Date],2)
weeknum = WEEKNUM('date _table'[Date],2)
weekno. = WEEKNUM('Table'[Week Start],2)

Then create measures.

average_ =
CALCULATE (
    SUM ( 'Table'[Sales Units] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[weekno.] = SELECTEDVALUE ( 'date _table'[weeknum] )
    )
) / 5
count_days =
CALCULATE (
    COUNT ( 'date _table'[monthcolumn] ),
    FILTER (
        ALL ( 'date _table' ),
        'date _table'[weeknum] = SELECTEDVALUE ( 'date _table'[weeknum] )
            && 'date _table'[monthcolumn] = SELECTEDVALUE ( 'date _table'[monthcolumn] )
            && 'date _table'[weekday] <= 5
    )
)
result =
MAXX (
    FILTER (
        ALL ( 'date _table' ),
        [count_days] <> 5
            && 'date _table'[monthcolumn] = SELECTEDVALUE ( 'date _table'[monthcolumn] )
    ),
    'date _table'[average_] * [count_days]
)

vpollymsft_0-1656573527757.png

Best Regards

Community Support Team _ Polly

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak
Super User
Super User

@dswinden , Create a date table in power query. Using week start or week end date. create this week column as text. and merge the two table. Same can be done in DAX

 

Or pouplate start and end date week in this table and use this blog - https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...

 

Power query calendar

let
    Source = List.Dates( StartDate,
                    Duration.Days( #date(2022,12,31)- #date(2018,1,1)) +1, 
                    #duration(1,0,0,0) ),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "StartOfMonth", each Date.StartOfMonth([Date])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "StartOfWeek", each Date.StartOfWeek([Date],2)),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "StartOfYear", each Date.StartOfYear([Date])),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "EndOfYear", each Date.EndOfYear([Date])),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Day of Week (Tue)", each Date.DayOfWeek([Date],2)+1),
    #"Added Custom5" = Table.AddColumn(#"Added Custom4", "DayOfWeekName", each Date.DayOfWeekName([Date])),
    #"Added Custom6" = Table.AddColumn(#"Added Custom5", "DayOfYear", each Date.DayOfYear([Date])),
    #"Added Custom7" = Table.AddColumn(#"Added Custom6", "Add days", each Date.AddDays([Date],-3)),
    #"Added Custom8" = Table.AddColumn(#"Added Custom7", "Add Months", each Date.AddMonths([Date],2)),
    #"Added Custom9" = Table.AddColumn(#"Added Custom8", "Current Time", each DateTime.LocalNow()),
    #"Added Custom10" = Table.AddColumn(#"Added Custom9", "Fixed Local Now", each DateTimeZone.FixedLocalNow()),
    #"Added Custom11" = Table.AddColumn(#"Added Custom10", "Todays date", each DateTime.Date(   DateTime.FixedLocalNow())),
    #"Added Custom12" = Table.AddColumn(#"Added Custom11", "Is In Current Day", each if Date.IsInCurrentDay([Date]) then "Today" else Date.ToText([Date])),
    #"Added Custom13" = Table.AddColumn(#"Added Custom12", "Is Current Month", each Date.IsInCurrentMonth([Date])),
    #"Added Custom14" = Table.AddColumn(#"Added Custom13", "Custom", each if Date.IsInCurrentMonth([Date]) then  "This Month" 
else if Date.IsInNextMonth([Date]) then "Next Month"
else if Date.IsInPreviousMonth([Date]) then "Last Month" else Date.ToText([Date], "MMM-yyyy")),
    #"Added Custom15" = Table.AddColumn(#"Added Custom14", "Next N month", each Date.IsInNextNMonths([Date],2)),
    #"Added Custom16" = Table.AddColumn(#"Added Custom15", "FY ", each if Date.Month([Date]) < 4 then #date(Date.Year([Date]) -1,4,1) else #date(Date.Year([Date]) ,4,1)),
    #"Added Custom17" = Table.AddColumn(#"Added Custom16", "Fy End", each if Date.Month([Date]) < 4 then #date(Date.Year([Date]) ,3,31) else #date(Date.Year([Date])+1 ,3,31)),
    #"Added Custom18" = Table.AddColumn(#"Added Custom17", "Day of Year", each Duration.Days([Date] -[#"FY "])+1),
    #"Added Custom19" = Table.AddColumn(#"Added Custom18", "Month Diff", each Number.FromText( Date.ToText( Date.AddMonths([StartOfMonth],-3),"yyyyMM") )
- Number.FromText( Date.ToText( Date.AddMonths([#"FY "],-3),"yyyyMM"))+1),
    #"Added Custom20" = Table.AddColumn(#"Added Custom19", "Fy Month", each if Date.Month([Date])>=4 then
Date.Month([Date])-Date.Month([#"FY "])+1
else Date.Month([Date])-Date.Month([#"FY "])+1+12)
in
    #"Added Custom20"

 


Merge: https://radacad.com/append-vs-merge-in-power-bi-and-power-query



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

Hi @amitchandak 

 

The start of the custom query gives me an error of "Expression.Error: The name 'StartDate' wasn't recognized. Make sure it's spelled correctly." when i try to use this for a custom column.  Is there another expression?

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors