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

[dswinden]

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 Start	Sales Units
May 23	127
May 30	143
June 6	128
June 13	100

[Anonymous · Accepted solution]

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]
)

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]

@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

[dswinden]

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?