cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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! 🙂

 

BookweekTY WEEKTY ACKLY WEEKLY ACK
1551010
2271020

3

310525
4 10530
5 10535
6 10742
7 10244
8 10751
total10105151

 

3 REPLIES 3
Community Support
Community Support

Hi @Teglbrander ,

 

Has your problem been solved?

 

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.

Community Support
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.

Regular Visitor

Annotation 2020-06-05 100810.png

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors