Calculating Total Sales of the last 4 weeks for each weeks (axis)

[NydelFyr]

Hello everybody. I'm struggling since a week on a dax formula.

For each week of my week axis, I would like to have the value of the production of the last 4 weeks of the week concerned.

I think i will have to use some loops, but i have no idea how to deal with.

The granularity of my database is the day.

Best regards,

Kilian

[Anonymous]

You should be careful when you're close to the beginning of your Calendar. What would you like to calculate if there are not enough days when you go back 4 weeks from the last visible day? You might want to blank out the measure. To do that you'll have to calculate the number of days returned by DATESINPERIOD and if it's not 4 * 7, then return a BLANK().

Best

Darek

[Anonymous · Accepted solution]

-- This measure sums up Value in the Production fact table:

[Total Production] =
	SUM( Production[Value] )
	
	
-- The Production table must have a date field on which
-- it'll join in a many-to-one fashion to a Calendar
-- date table (that is marked as Date Table).
-- In the Calendar the granularity will
-- be a single day (obviously).

-- Let's say you've selected for your x-axis the
-- unique name of the week. Then your measure will be:

[Production L4W] = -- L4W = Last 4 Weeks
var __lastDayVisible = LASTDATE( 'Calendar'[Date] )
var __numOfDaysInPeriod = 7 * 4 - 1 --> -1 since the last day counts as well!
var __production = 
	CALCULATE(
		[Total Production],
		DATESINPERIOD(
			'Calendar'[Date],
			__lastDayVisible,
			- __numOfDaysInPeriod,
			DAY
		)
	)
return
	__production
	
-- By the way, this will work for any periods you select
-- from Calendar. It'll first grab the last day in the
-- period and then will calculate the total production
-- for the period from (the last visible date - 7 * 4 days + 1)
-- up to and including the last visible date. This interval
-- has exactly 4 * 7 days.

Best

Darek