Forum Discussion
Calculating Spend Last week
Hi there, please can anyone help. I am trying to create a measure calculating Spend LW. The issue is my stakeholders want it in such a way that they can select multiple weekstart dates and the Spend ...
First create a date table and mark it as a date table. Create a relationship from 'Date'[Date] to your 'Test'[Calendar Date] and then you can create a measure like
Spend Last Week =
VAR EndDate = MIN('Date'[Date])
VAR NumSelected = COUNTROWS(VALUES('Date'[W / C]))
VAR StartDate = EndDate - (7 * NumSelected)
VAR DatesToUse = DATESBETWEEN(
'Date'[Date],
StartDate,
EndDate - 1
)
VAR Result = CALCULATE(
SUM('TEST'[SPEND]),
DatesToUse
)
RETURN
ResultSee the attached PBIX.
Thank you so much! This works perfectly!