Calculating Spend Last week

[leekarensl]

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 LW should automatically calculate the sum of spend for the x weeks before.  In the attached file example, if 27th oct and 3rd Nov is being selected by the end user, the Spend LW will calculate the sum of spend for 13th Oct and 20th Oct. And if on the filter, 3rd Nov, 27th Oct, 20th Oct and 13th oct is selected, then Spend LW will calculate the sum of spend for 6th Oct, 29th Sep, 22nd Sep and 15th Sep. I can't seem to get it to work and would appreciate any help. The point is the end user wants to have the flexibility to select how ever many or few weekstartdates from the weekstartdate filter and the Spend LW should be able to calculate accordingly.

The file can be found here  Thanks very much in advance.

[Bibiano_Geraldo]

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
)

[johnt75 · Accepted solution]

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.

[leekarensl]

Thank you so much! This works perfectly!

[Kedar_Pande]

@leekarensl 

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

[leekarensl]

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]

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