Trying to get a week to date # but last year sales goes away

[OCBB_SFAFPandA]

I have a measure to get the current yr in a calculated date table

This Year = IF('Date'[Date].[Year] = MAX('Date'[Date].[Year]), "This Year", FORMAT('Date'[Date].[Year], "####"))

 I have a week measure in our retail calendar (adjusts for retail weeks) that I use to match same week and year

This week w. leap = 
IF('DIM_Calendar_From_2010_To_2100'[Week Number] = WEEKNUM(MAX('Date'[Date]),1)-1,"This Week",
    FORMAT('DIM_Calendar_From_2010_To_2100'[Week Number],"##"))

this works fine at the end of the week, but during the current week, the last year sales are fully calculated, and this week's sales only has 1 day or too. Is there a way to have last year show only up to the days this year sales shows?

I tried putting my week filter in the calculated date table, but it calculated a day prior to what it's supposed to be compared to. 

I also tried to tweak the week column in my retail table to only get the max date, but then this year sales don't show

IF(DIM_Calendar_From_2010_To_2100[TheDate] = MAX('date'[date]) &&
    'DIM_Calendar_From_2010_To_2100'[Week Number] = WEEKNUM(MAX('Date'[Date]),1)-1,"This Week",
    FORMAT('DIM_Calendar_From_2010_To_2100'[Week Number],"##")

[v-chenwuz-msft]

Hi @OCBB_SFAFPandA ,

To get the same days in the last year week as current week days from begining to today? For example, today is Tuesday and weeknum 25, the date range should be Sunday to Tuesday in week 25 of last year?

If this week =
VAR _max =
    MAX ( 'Date'[Date] )
RETURN
    IF (
        AND (
            DIM_Calendar_From_2010_To_2100[TheDayOfWeek] <= WEEKDAY ( _max, 1 )
                && 'DIM_Calendar_From_2010_To_2100'[Week Number]
                    = WEEKNUM ( _max, 1 ) - 1,
            'DIM_Calendar_From_2010_To_2100'[Date] <= _max
        ),
        "This week",
        FORMAT ( 'DIM_Calendar_From_2010_To_2100'[Week Number], "00" )
    )

Best Regards

Community Support Team _ chenwu zhu

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

[OCBB_SFAFPandA · Accepted solution]

Think I got it, it was an issue with LY sales using sameperiodlast year and my filters.

Able to get it with this link

https://summalai.com/?p=4036 

[OCBB_SFAFPandA]

Thanks for your reply.  Yes, that's correct. 

My page is set up like this

Slicer for Year and week
Table

Location	Sales this year	Last year
1	$150	$100

right now I have a measure that grabs the WTD (in your example, Sun-Tues) and returns a sales amount. The problem is that in the table last year sales shows the full week (Sun-Sat). 

I tried another measure that puts the current year in the formula, but then last year sales totals doesn't show in the tables. 

[OCBB_SFAFPandA]

this is shown as a weekly view, but i added the daily data that rolls up to it

[OCBB_SFAFPandA]

My code is up to this point, but it is filtering out any prior year current weeks. I tried a function to equal todays' year, but then my last year sales numbers don't show up.

	IF(
        DIM_Calendar_From_2010_To_2100[TheDayOfWeek] <= weekday(MAX('date'[date]),1) &&
	    'DIM_Calendar_From_2010_To_2100'[Week Number] = WEEKNUM(MAX('Date'[Date]),1)-1,"This Week",
    FORMAT('DIM_Calendar_From_2010_To_2100'[Week Number],"##"))