Calculating Sales By Period to Date in Power BI

[Belmonte]

Hi Guys,

Could you help?

I'm trying to create a sales report which summarises the sales by Week, Period to date & YTD. I've managed to calculate the YTD it's the Period to date I'm having the issues with.



In the calendar table above, I've selected Week 6 (Let's say we have sold 10 units every week to make it easier to understand) So week 6 would 10 units. Period to date would be 20 Units & YTD would be 60 Units.

Below is my measure I used to calculate the YTD units, which works.

For this, I created a couple of other measures, the first measure [WeekMin] takes the minimum week number, the second measure [SelectedWeek] does what it says, uses the selected week from the slicer. The sums all the weeks from week 1 to the week selected:

Sales YTD =
CALCULATE (
[Weekly Sales],
FILTER (
'Sales Table',
'Sales Table'[Time.Week of Year] >= [WeekMin]
&& 'Sales Table'[Time.Week of Year] <= [WeekSelected]
)
)

So basically I would like to calculate the Period to date based on the currently selected week. A few examples:

If I selected week 4 the Period to date units would be 40.
If I selected week 7 the Period to date units would be 30.

Just to note not all periods are 4 weeks, some are 5 weeks.

Many thanks for you help!

L

 

[Chihiro]

Without knowing full model, bit hard to say...
But can't you just use "Period" in your measure rather than Week?

 

[Belmonte]

Hi Chihiro,

Thanks for your response.

I have changed it from Week to Period, which works, however, when I compare the figures to last year, if the current year is only 2 weeks into a period & you compare it to last year the YOY comparison isn't correct because LY's figures contains all of the 4 completed 4 weeks. If there is a work around to this I'm all ears!

Thanks again!

 

[Chihiro]

When using previous year comparison, you will need to check for MAX of Week num for current year.
Then filter the previous year's period based on Week < MAX Week of current year.
So something like...
=CALCULATE(Calculation,FILTER(DimDate,Period=CurrentYearPeriod&&Year=PreviousYear&&Week<=MAX Week))

NOTE: This assumes Period and Week Num align year over year.

 

[Belmonte]

Thanks for that, I will give a go!

 

[Belmonte]

Hi Chihiro, that worked like a dream! Appreciate your assistance with this.
Thanks again!