cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

Calculate weekly last season sales

Hi all

Tryed to find an already posted solution, but didn't succeed.

I'm trying to make a calculation to my table that shows last season weekly sales. Its the value "LY WEEK" in below table that I can't seem to create a meassure for.

The booking week is taken from a booking date table.

Sales data from a Sales Table where cancel date as well is shown for cancelled sales. If for example one is booked in week 1, but cancelled in week 3, the sales value of both WEEK and ACK should be included in book week 1+2 and the removed again in week 3 as it is cancelled that week.

I tried with the following meassure, but I recieve an error:

LY WEEK = CALCULATE(
SUM(SALES[AMOUNT]),
ISBLANK('SALES'[CancellationDate]) || FILTER(ALL(SALES),SALES[CancellationDate] > SAMEPERIODLASTYEAR(Bookingdate[Date])),
SAMEPERIODLASTYEAR('Depdate'[Date])

Pls help! 🙂

 Bookweek TY WEEK TY ACK LY WEEK LY ACK 1 5 5 10 10 2 2 7 10 20 3 3 10 5 25 4 10 5 30 5 10 5 35 6 10 7 42 7 10 2 44 8 10 7 51 total 10 10 51 51

3 REPLIES 3
Highlighted
Regular Visitor

Highlighted
Community Support

Hi @Teglbrander ,

Maybe you can try this measure.

`````` LY WEEK =
CALCULATE(
SUM(SALES[AMOUNT]),
SAMEPERIODLASTYEAR('Depdate'[Date]),
FILTER(
ALL(SALES),
SALES[CancellationDate] > SAMEPERIODLASTYEAR(Bookingdate[Date]) ||  ISBLANK('SALES'[CancellationDate]) = TRUE()
)
)``````

If this formula is incorrect, would you mind sharing your .pbix file?

Best regards,
Lionel Chen

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

Highlighted
Community Support

Hi @Teglbrander ,

Best regards,
Lionel Chen

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

Announcements

Power Platform Community Conference

Check out the on demand sessions that are available now!

Microsoft Power Platform Communities

Check out the Winners!

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors