cancel
Showing results for
Did you mean: 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 Start Sales Units May 23 127 May 30 143 June 6 128 June 13 100
1 ACCEPTED SOLUTION  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]
)
`````` 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.

3 REPLIES 3  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]
)
`````` 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.  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}}),
else if Date.IsInNextMonth([Date]) then "Next Month"
else if Date.IsInPreviousMonth([Date]) then "Last Month" else Date.ToText([Date], "MMM-yyyy")),
#"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)),
Date.Month([Date])-Date.Month([#"FY "])+1
else Date.Month([Date])-Date.Month([#"FY "])+1+12)
in

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 !! Frequent Visitor

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? Announcements #### The Power BI Community Show

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