Extract Weekly Sales volume from Daily transaction table

Hi Upali63 ,

--1--

In Power Query, select your Daily Table query and go to the Add Column tab > Custom Column. Call it 'Turnover' and use the following calculation:

[Price] * [Qty]

--2--

Then create a calendar table in Power Query (many examples online) with a [WeekEndingDate] column. In PQ, you would create it like this:

Date.EndOfWeek([Date], Day.Friday)

Adjusting the 'Day.Friday' argument to the desired first day of the week.

--3--

Apply all to the data model and relate:

Calendar[Date] to WeeklyTable[W-End]

Calendar[Date] to DailyTable[Date]

--4--

Create a measure for your Turnover value:

_turnover = SUM(DailyTable[Turnover])

--5--

In a table visual, add these fields:

Calendar[WeekEndingDate]

WeeklyTable[All columns except [W-End] ]

...and add this measure:

[_turnover]

This should do what you want.

Pete