topic Trying to get a week to date # but last year sales goes away in DAX Commands and Tips

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

OCBB_SFAFPandA — Mon, 13 Jun 2022 19:23:45 GMT

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],"##")

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

OCBB_SFAFPandA — Tue, 14 Jun 2022 16:03:57 GMT

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],"##"))

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

v-chenwuz-msft — Thu, 16 Jun 2022 08:59:50 GMT

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.

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

OCBB_SFAFPandA — Thu, 16 Jun 2022 16:01:57 GMT

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.

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

OCBB_SFAFPandA — Thu, 16 Jun 2022 19:13:59 GMT

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

OCBB_SFAFPandA — Fri, 17 Jun 2022 18:16:50 GMT

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