Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

WTD Last Year Calculation

Hello, 

 

I am trying to compute WTD Last Year for Sales Data. I have computer WTD this year using either or these formulas: 

 

WTD TY Sales = CALCULATE(SUM([Dollars_Sold]),DATESINPERIOD(Sales[Calendar Date],LastDate(Sales[Calendar Date]),-WEEKDAY(LastDate(Sales[Calendar Date])),DAY))

 

or

 

WTD TY Sales = CALCULATE(SUM(Sales[Dollars_Sold]),LASTDATE(Sales[Calendar Date]))

 

I have tried to adjust these formulas to show me same WeekNumber last year (ie 2017), but I have not been able to do it. Does anyone know how I can compute it?

  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    Hi Anonymous,

     

    Please check out the demo in the attachment and try the measure like this.

    Measure =
CALCULATE (
    SUM ( FactSales[salesquantity] ),
    FILTER (
        ALL ( 'Calendar' ),
        YEAR ( [Date] )
            = YEAR ( MIN ( [Date] ) ) - 1
            && 'Calendar'[WeekNum] = MIN ( 'Calendar'[WeekNum] )
    )
)

    WTD-Last-Year-Calculation

    Best Regards,
    Dale

11 Replies

  • Chihiro's avatar
    Chihiro
    Solution Sage
    7 years ago

    It is near impossible to align weeks perfectly over different year.

     

    Ex:

    Using WEEKNUM([Date], 21) i.e. ISO 8601 week number.

    Year 2020 will have week number 53. Where as 2017 to 2019 will have 52.

     

     

    When comparing different years, I tend to use one of following methods depending on the need.

     

    1) Average of value by weekday in a given month of year and compare to previous year.

    2) Compare at monthly level

    3) Compare based on day of year (i.e. 1~365/366), if there is no intra week pattern.

    4) Compare week over week, without year constraint.

    etc.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      We are in a retail environment, so we are using a 4-5-4 calendar. 

       

      So we will be able to compare the same week number in different years. 

      • Chihiro's avatar
        Chihiro
        Solution Sage
        7 years ago

        Hmm? Would some year not have 53 weeks instead of 52 weeks in 4-5-4 format still? Occuring every 5 to 6 years?

         

        Do you have dimension table where 2017 is restated (i.e. ignoring first week of the year 2017)? Or is 53rd week being ignored?

         

        At any rate, it would help if you have model along with calendar dimension table (4-5-4 mapping).

         

  • kazael's avatar
    kazael
    Helper I
    7 years ago

    Are you trying to present sales between the same week number last year and current date?

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Yes! We are trying to present sales during the same week TY vs. LY

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Microsoft Employee
        7 years ago

        Hi Anonymous,

         

        Please check out the demo in the attachment and try the measure like this.

        Measure =
CALCULATE (
    SUM ( FactSales[salesquantity] ),
    FILTER (
        ALL ( 'Calendar' ),
        YEAR ( [Date] )
            = YEAR ( MIN ( [Date] ) ) - 1
            && 'Calendar'[WeekNum] = MIN ( 'Calendar'[WeekNum] )
    )
)

        WTD-Last-Year-Calculation

        Best Regards,
        Dale

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    7 years ago

    Hi Anonymous,

     

    Could you please mark the proper answers as solutions?

     

    Best Regards,

    Dale