DAX for last week as (sat - sun) week

[Justas4478]

Hi, I am trying to create measure to show me data from last week.

Actual last week = CALCULATE(SUM('Outbound Delivery'[Actual]),

FILTER('Date','Date'[Date] = WEEKDAY(TODAY()-7)))

The problem is it does not work and on top of that I would want last week data to be Sun-Sat not Monday - sunday if possible.
I cant edit or create columns since it is live connection model that I am using.
I dont know if this is doable with only measures and not calculated columns.

Thanks

[rajendraongole1 · Accepted solution]

Hi @Justas4478 -create a measure that calculates the sum of 'Actual' from the last week (Sunday to Saturday).

created two variables for sun and saturday., try the below logic

i am assuming 1 means Sunday=1, Monday=2, ..., Saturday=7

Measure:

Actual Last Week =
VAR TodayDate = TODAY()
VAR TodayWeekday = WEEKDAY(TodayDate, 1) 
VAR LastSaturday = TodayDate - TodayWeekday
VAR LastSunday = LastSaturday - 6
RETURN
CALCULATE(
SUM('Outbound Delivery'[Actual]),
DATESBETWEEN('Date'[Date], LastSunday, LastSaturday)
)

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[Justas4478]

@rajendraongole1 The measure is working and it is returning correct numbers so far as I dont use any dates in my tables or visuals.
This is what I get if I use dates in visual:

 
The Total is correct just it is not split by date.

Is it possible to to me them shown by date?

[rajendraongole1]

Hi @Justas4478 -make sure you have a proper date dimension table date that is connected to your 'Outbound Delivery' table on the date field. The measure will then sum up the 'Actual' values for the last week's Sunday to Saturday period correctly based on the context of the visual.

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[Justas4478]

I found temporary solution for this date dimension problem.
I had to add this line in to the dax.

TREATAS(VALUES('Outbound Delivery'[DateKey]),'Date'[DateKey])

Unfortunately as long term solution there might need to be checked relationships between tables.

[Justas4478]

@rajendraongole1 This could be ralated to one of existing problems that is being adressed in the live data that I use.
Any way thanks for the solution.

[Justas4478]

@rajendraongole1 It looks like to be working.
If I want to get data from  2 weeks back.
If last week data was 16 June - 22 June.
Two weeks back would be from 09 June - 15 June.

To get this do I just add -7 to this part of dax? VAR TodayDate = TODAY()

Thanks