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.

johnt75's avatar
johnt75
Super User
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

Thank you so much! This works perfectly!