topic Re: Last N weeks sales for same period last year in DAX Commands and Tips

Last N weeks sales for same period last year

Jng_06 — Wed, 01 May 2024 02:49:21 GMT

Hello.

I'm trying to calculate year over year for last N weeks.

I have a helper table that create the group of the last N weeks so I can use it for slicers and matrix table.

I have created a dax calculating Last N weeks as follow:

Sum Last N Weeks Sales =

var SelectedWeekOptionsValue = SELECTEDVALUE('Last N Weeks'[Last N Weeks ])

var CurrentYearWeek = max('Fiscal Calendar'[week from today])

return CALCULATE(SUM('Sales Line'[Amt]), 'Fiscal Calendar '[week from today] >0 ,

'Fiscal Calendar'[week from today]<=SelectedWeekOptionsValue

&& 'Fiscal Calendar '[week from today] > SelectedWeekOptionsValue - CurrentYearWeek)

However when I try to use the same logic for the same period last year, the dax is not calculating the previous correct correctly.

Sum Last N Weeks Sales Last Year =

VAR SelectedWeekOptionsValue = SELECTEDVALUE('Last N Weeks'[Last N Weeks ])

VAR CurrentYearWeek = MAX('Fiscal Calendar'[week from today])

VAR LastYearWeek = CALCULATE(MAX('Fiscal Calendar'[week from today]), SAMEPERIODLASTYEAR('Fiscal Calendar '[FULL_DATE]))

RETURN

CALCULATE(

SUM('Sales Line'[Amt]),

'Fiscal Calendar Taurus'[week from today] <= LastYearWeek &&

'Fiscal Calendar Taurus'[week from today] > LastYearWeek - SelectedWeekOptionsValue

)

*** Fiscal Calendar'[week from today] column is calculated as follow :

week from today = DATEDIFF('Fiscal Calendar'[FULL_DATE]-1,TODAY(),WEEK) ***

Any insights on how I can improve my second dax calculation on Last N Weeks previous year?

Thank you!

Re: Last N weeks sales for same period last year

amitchandak — Wed, 01 May 2024 03:01:56 GMT

Jng_06 , Make sure the table also have number along with text so that you can get last few weeks as number

Last N Week Today =
var _min = today()// Or add to get monday -WEEKDAY(today() ,2) -6 //Monday week start
var _max = _min +6 * SELECTEDVALUE('Last N Weeks'[Last N Weeks ])
return CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))

Last N Week Today Last year =
var _min = date(year(today())-1, Month(Today()) , day(Today() ) ) // Or add to get monday -WEEKDAY(today() ,2) -6 //Monday week start
var _max = _min +6 * SELECTEDVALUE('Last N Weeks'[Last N Weeks ])
return CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))

WOW with help from selected dates

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8
Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510

Re: Last N weeks sales for same period last year

Jng_06 — Wed, 01 May 2024 16:33:24 GMT

Hello

Thank you for your reply.

I tried and not sure if I missed anything, the dax is still returning blanks.

My version right now is good on the current year but when I modify it to look at previous year its still returning the result but it look at 4 years ago instead of 1 year

Sum Last N Weeks Sales Last Year =

VAR SelectedWeekOptionsValue = SELECTEDVALUE('Last N Weeks'[Last N Weeks ])

VAR CurrentYearWeek = MAX('Fiscal Calendar'[week from today])

VAR LastYearWeek = CALCULATE(MAX('Fiscal Calendar'[week from today]), SAMEPERIODLASTYEAR('Fiscal Calendar '[FULL_DATE]))

RETURN

CALCULATE(

SUM('Sales Line'[Amt]),

'Fiscal Calendar Taurus'[week from today] <= LastYearWeek &&

'Fiscal Calendar Taurus'[week from today] > LastYearWeek - SelectedWeekOptionsValue

)

Re: Last N weeks sales for same period last year

Anonymous — Fri, 10 May 2024 07:14:35 GMT

amitchandak Thanks for your contribution on this thread.

Hi Jng_06 ,

You can update the formula of your measure [Sum Last N Weeks Sales Last Year] as below and check if you can get the expected result.

Sum Last N Weeks Sales Last Year = VAR SelectedWeekOptionsValue = SELECTEDVALUE ( 'Last N Weeks'[Last N Weeks ] ) VAR CurrentYearWeek = MAX ( 'Fiscal Calendar'[week from today] ) VAR LastYearWeek = CALCULATE ( MAX ( 'Fiscal Calendar'[week from today] ), SAMEPERIODLASTYEAR ( 'Fiscal Calendar '[FULL_DATE] ) ) RETURN CALCULATE ( SUM ( 'Sales Line'[Amt] ), FILTER ( ALLSELECTED ( 'Fiscal Calendar Taurus' ), 'Fiscal Calendar Taurus'[week from today] <= LastYearWeek && 'Fiscal Calendar Taurus'[week from today] > LastYearWeek - SelectedWeekOptionsValue ), ALLSELECTED ( 'Sales Line' ) )

If the above ones can't help you figure out, please provide some raw data in your table ''Sales Line' (exclude sensitive data) with Text format and your expected result with backend logic and special examples. By the way, please provide the related relationship info if there is any relationship between the table 'Sales Line' and calendar tables. It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And it is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards