Post reply

Get current and previous week sales

  • June 20, 2020 at 4:20 am

    #3764477

    Hi,

    I have a FactBooking (BookingDateKey, Amount, Qnt) and a dimension DimDate(DateKey,Year,Month,WeekNumberOfYear) tables.

    Can you please let me know the sql to get the current and next week bookings?

    Thanks

    Subbu

  • June 20, 2020 at 12:25 pm

    #3764506

    This is close, but it needs to be changed for the first week of year/last week of previous year comparison.

    =CALCULATE( [Total Sales], 
               FILTER (ALL('DimDate'),
                   'DimDate'[CalendarYear] = MAX('DimDate'[CalendarYear]) &&
                   'DimDate'[WeekNumberOfYear] = MAX('DimDate'[WeekNumberOfYear])
                   )
             )

    If your Calendar table includes a monotonic WeekNumber that spans your entire calendar, then you could just subtract 1 from it

    =CALCULATE( [Total Sales], 
               FILTER (ALL('DimDate'),
                   'DimDate'[WeekNumberAllTime] = MAX('DimDate'[WeekNumberAllTime]) - 1
                   )
             )

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply