Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

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 ...
Need Help
  • johnt75's avatar
    johnt75
    1 year ago

    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
    Result

    See the attached PBIX.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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 :

johnt75's avatar
johnt75
Super User
1 year ago

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