Same Period Last Year when Sales Data is by Week Ending Date

[Mainer04401]

I'm struggling with how to calculate sales in the same period year ago because my data has sales by week ending date, not by day. 

For example, I want to calculate sales in the same period year ago of CYTD through 7/18/2020.  Week ending 7/18/2020 is the 29th week of 2020.  The 29th week of 2019 ends 7/20/2019, but SamePeriodLastYear will try to find data through 7/18/2019.  

I made a sample report but I don't know how to attach it.  Below is what my date-table looks like.  I can modify it however needed to make this work.  

Week Ending	Week Number	Calendar Year	Month	Quarter	Month / Year	Quarter / Year	2020 Calendar Week Ending
1/5/2019	1	2019	January	Q1	2019 January	2019 Q1	1/4/2020
1/12/2019	2	2019	January	Q1	2019 January	2019 Q1	1/11/2020
1/19/2019	3	2019	January	Q1	2019 January	2019 Q1	1/18/2020
1/26/2019	4	2019	January	Q1	2019 January	2019 Q1	1/25/2020
2/2/2019	5	2019	February	Q1	2019 February	2019 Q1	2/1/2020
2/9/2019	6	2019	February	Q1	2019 February	2019 Q1	2/8/2020
2/16/2019	7	2019	February	Q1	2019 February	2019 Q1	2/15/2020
2/23/2019	8	2019	February	Q1	2019 February	2019 Q1	2/22/2020
3/2/2019	9	2019	March	Q1	2019 March	2019 Q1	2/29/2020

[amitchandak]

@Mainer04401 ,

refer these two

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3

[Mainer04401]

I'm getting close ...  I used the formula below to get same week year ago, however I need same period year ago when multiple weeks are selected.  So, for example, if CYTD is week numbers 1 through 29, I need the sum of sales in weeks 1 through 29 last year.

LYWTD Year = CALCULATE([Sales], FILTER(ALL(‘Date’),’Date’[Year]=(max(‘Date’[Year]) -1) && ‘Date’[Week Number]=(max(‘Date’[Week Number])) && ‘Date’[Weekday] <=max(‘Date’[Weekday])))

[Mainer04401]

Yessss, I finally got it!  For anyone who finds this from search, here is the formula to find last year's sales during the same WEEK NUMBERS as this year.  This works for individual weeks as well as an aggregate of several weeks.

Prev Year Sales =

CALCULATE(

    [Sum of Sales],

    FILTER(

        ALL('Date Table'),

        'Date Table'[Calendar Year] = MAX('Date Table'[Calendar Year]) -1

        && 'Date Table'[Week Number] >= min('Date Table'[Week Number])

        && 'Date Table'[Week Number] <= max('Date Table'[Week Number])

    )

)

EDIT: Nope, I got too excited...  This formula works unless the weeks selected span multiple years.  For example, last 13 weeks as of the end of January 2020.  It determines the minimum week number is 1, (first week of January 2020) and the maximum week number is 52 (last week of Dec 2019), so it returns total sales in 2019.  I'm thinking maybe I add a cumulative week # column in my dates table?  Then instead of filtering by year, I filter by -52 of the min & max cumulative week number?  Any other ideas, please let me know.  I feel like I'm now over-complicating it.  

[Greg_Deckler]

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[Mainer04401]

Thanks but unfortunately I couldn't get that to work.  It worked without any weeks in the context or without any filters, but when I start filtering the weeks, the output went black.

Here's the DAX I used:

Yago Sales = VAR __MaxYear = MAX('Date Table'[Calendar Year])

VAR __MaxWeek = MAX('Date Table'[Week Number])

VAR __TmpTable = CALCULATETABLE('Date Table',ALL('Date Table'[Calendar Year],'Date Table'[Week Number]))

RETURN SUMX(FILTER(__TmpTable,[Calendar Year]=__MaxYear-1 && [Week Number] <= __MaxWeek),[Sum of Sales])