Forum Discussion

MarkCBB's avatar
MarkCBB
Icon for Helper V rankHelper V
9 years ago
Solved

Last Year This/Same Week

Hello there,   I created the follow DAX Measure to calculate This Year This Week, I am sure there is a better way to do this, but this does work: TYTW Units = CALCULATE([Total Units],FILTER('CALEN...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    How is [Week Year ID] stored?  Could you build last years [Week Year ID] by making use of the same one you used in your TWTY measure?

    For Example if [Week Year ID] is YYYYWW you could build the string using a combination of LEFT and RIGHT to get the Year and Week components, then decrement the YYYY by one and build a new filter string from there.

MarkCBB's avatar
MarkCBB
Icon for Helper V rankHelper V
9 years ago

ooo, that is a clever approach.

It is stored as an INT (Whole number), download link below:

Calendar

 

That said, all I need to do is change the Year Week ID to the format you proposed and I should be able to implement that approach.

 

Going to try now, I have not tried to use LEFT/RIGHT into a Measure before, let me give it a go and I will post back.

 

v-caliao-msft's avatar
v-caliao-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

MarkCBB,

 

Since there are no PREVIOUSWEEK function in DAX.

 

So if need to get last year same week, you need to calculate the Year and week and weektotal in your data model, and then use LOOKUPVALUE function to get previous week value and last year same week value.

I have two tables in my sample data model. In your date table, create two columns.
Year = YEAR('Date'[CalendarDate])
WeekNumber = WEEKNUM('Date'[CalendarDate])

then create a new table
Table = SUMMARIZE('Date','Date'[Year],'Date'[WeekNumber],"Weektotal",calculate(SUM(Sales[SalesAmount]),ALLEXCEPT('Date','Date'[Year],'Date'[WeekNumber])))
Create three measures.
WeekTotalSaleAmount = calculate(SUM(Sales[SalesAmount]),ALLEXCEPT('Date','Date'[Year],'Date'[WeekNumber]))
PreviousWeek = LOOKUPVALUE('Table'[Weektotal],'Table'[Year],IF(MAX('Date'[WeekNumber])=1,MAX('Date'[Year])-1,MAX('Date'[Year])),'Table'[WeekNumber],IF(MAX('date'[WeekNumber])=1,53,MAX('Date'[WeekNumber])-1))

PreviousYearWeek = LOOKUPVALUE('Table'[Weektotal],'Table'[Year],MAX('date'[Year])-1,'Table'[WeekNumber],MAX('date'[WeekNumber]))

 

Capture.PNG Capture1.PNG

 

Regards,

Charlie Liao

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    This solution works great, but I have a newbie question. How would you adapt it to add in a category. For example I would like the PreviousYearWeek to return the number of Bikes sold the same week last year and the number of balls. Right now it seems to total my groups by week.