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.

Bibiano_Geraldo's avatar
Bibiano_Geraldo
Super User
1 year ago

Hi, 
Please try the following dax code:

Spend LW = 
VAR SelectedWeeks = VALUES('TEST'[WeekStartDate])
VAR NumberOfWeeks = COUNTROWS(SelectedWeeks) * 7  // Dynamic offset: number of weeks * 7 days
VAR PreviousWeeks = 
    CALCULATETABLE(
        VALUES('TEST'[WeekStartDate]),
        FILTER(
            ALL('TEST'),
            'TEST'[WeekStartDate] IN 
                SELECTCOLUMNS(
                    SelectedWeeks,
                    "PreviousWeek",
                    IF(
                        HASONEVALUE(TEST[WEEKSTARTDATE]),
                            'TEST'[WeekStartDate] - 7,
                            ('TEST'[WeekStartDate]-7) - NumberOfWeeks
                    )
                )
        )
    )
RETURN
CALCULATE(
    SUM('TEST'[SPEND]),
    'TEST'[WeekStartDate] IN PreviousWeeks
)