cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Helper I

## Calculated Column for Previous Week Sales

Hi,

I am struggling to write dax for a calculated column, not a measure, that will provide me with the previous week's subtotal sales for a user id.  My data table is structured like this:

 UserID Sales Week Ending 1 5 1/8/2022 2 2 1/8/2022 1 1 1/1/2022 3 3 1/1/2022 3 2 1/8/2022 1 2 1/1/2022 2 4 1/1/2022 4 1 1/8/2022 3 2 1/1/2022 2 1 1/1/2022 1 6 1/1/2022 2 3 1/8/2022

So, for example, in the first Row, I want to be able to show in a calculated column that UserID 1 had a total of 9 sales in the previous week (1/1/2022).  How can I accomplish this, dynamically based on the week ending listed in the table?

1 ACCEPTED SOLUTION
Responsive Resident

In that case you could rewrite the column to act as a binary flag.

``````Prev Week Sales Flag =
VAR CurID = 'table'[UserID]
VAR CurWeek = 'table'[Week Ending]
VAR PrevWeek = CurWeek - 7

VAR LastWeekSales =
CALCULATE(
SUM('table'[Sales]),
FILTER(
ALL('table'),
'table'[UserID] = CurID &&
'table'[Week Ending] = PrevWeek
)
)

RETURN
SWITCH(
TRUE(),
LastWeekSales > 5, 1
0
)``````
3 REPLIES 3
Responsive Resident

In that case you could rewrite the column to act as a binary flag.

``````Prev Week Sales Flag =
VAR CurID = 'table'[UserID]
VAR CurWeek = 'table'[Week Ending]
VAR PrevWeek = CurWeek - 7

VAR LastWeekSales =
CALCULATE(
SUM('table'[Sales]),
FILTER(
ALL('table'),
'table'[UserID] = CurID &&
'table'[Week Ending] = PrevWeek
)
)

RETURN
SWITCH(
TRUE(),
LastWeekSales > 5, 1
0
)``````
Responsive Resident

This will always provide the sum of sales for the prior week for each user ID.

``````Prev Week Sales =
VAR CurID = 'table'[UserID]
VAR CurWeek = 'table'[Week Ending]
VAR PrevWeek = CurWeek - 7

RETURN
CALCULATE(
SUM('table'[Sales]),
FILTER(
ALL('table'),
'table'[UserID] = CurID &&
'table'[Week Ending] = PrevWeek
)
)``````

Just curious why you can't use a measure here?

Helper I

I'm looking to use it as a filter in another measure, at the UserID level - basically if last week's sales for that userID are >5, then don't count them.

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors