Forum Discussion
Calculating Spend Last week
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.
Anonymous
Create a calculated column
PreviousWeekStartDate =
CALCULATE(
MAX(YourTable[WeekStartDate]),
FILTER(
YourTable,
YourTable[WeekStartDate] < EARLIER(YourTable[WeekStartDate])
)
)
Create a measure
SpendLW =
VAR SelectedWeekStartDates = VALUES(YourTable[WeekStartDate])
VAR PreviousWeekStartDates =
CALCULATETABLE(
VALUES(YourTable[PreviousWeekStartDate]),
FILTER(
YourTable,
YourTable[WeekStartDate] IN SelectedWeekStartDates
)
)
RETURN
CALCULATE(
SUM(YourTable[Spend]),
YourTable[WeekStartDate] IN PreviousWeekStartDates
)
Apply the measure to your report to reflect the spend for the previous weeks based on the selected week start dates.
💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn
Thanks for your prompt response but it is not calculating the figures correctly. If I select 13 Oct and 6th Oct, the values are as follows:
If I selected 20th and 27th Oct, then the Spend LW column should give me exactly the figures above but it is giving me :I think you're still using the [Spend LW] measure, not the [Spend Last Week] measure which uses the code I wrote. Check Duplicate of Page 1 in the PBIX