Skip to main content
cancel
Showing results for 
Search instead 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

Reply
jackj
Helper I
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:

 

UserIDSalesWeek Ending
151/8/2022
22

1/8/2022

111/1/2022
331/1/2022
321/8/2022
121/1/2022
241/1/2022
411/8/2022
321/1/2022
211/1/2022
161/1/2022
231/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
AUaero
Responsive Resident
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
)

View solution in original post

3 REPLIES 3
AUaero
Responsive Resident
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
)
AUaero
Responsive Resident
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?

 

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.

Helpful resources

Announcements
Fabric Community Conference

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.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

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