cancel
Showing results for
Did you mean:
Highlighted

## Perform calculation on customers that were active in both periods

I'm trying to calculate the change in the total energy usage from the previous week to the current week, but I only want to take into consideration those customers that have been active with us for both weeks.  So not taking into account those customers that were active only in one of those weeks.  Any ideas on a kind of formula I can use to accomplish this?

Thank you!

7 REPLIES 7

## Re: Perform calculation on customers that were active in both periods

interesting problem

what granularity is your sales data?  Daily or weekly?

the trick to Dax is to think about "filtering" first, calc second. So how can you filter the data so only customers with sales in both weeks exist, then do the calc.

I'm thinking summarize to create a list of customer sales, then addcolumns to add distinctcount of #weeks in the last 2 weeks, then filter to remove the 1s.

If you post some samole data I will have a go for you.

* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Post Prodigy

## Re: Perform calculation on customers that were active in both periods

Creating a conditional column in query viewer might be a good option as so you have a column labelling them Active or NonActive {Energy consumption>0?].

You can then use a bit of code Matt above gave me:

```P1WNetSales =
VAR CurrentWeekNum =
MAX ( 'ExtendedCalendar'[WeekNum] ) - 1
VAR CurrentYear =
MAX ( 'ExtendedCalendar'[Year] )
RETURN
(
CALCULATE (
SUM ( 'itemdetailsdogfood\$'[Net Sales] ),
FILTER (
ALL ( 'ExtendedCalendar' ),
ExtendedCalendar[Year] = CurrentYear
&& 'ExtendedCalendar'[WeekNum]
= CurrentWeekNum - 0
)
)
)

```

This is for the last week.

You'll need a date table as well as column in your date table which corresponds to the week number. WEEKNUM the datekey of your table.

At this point though; you might need to fiddle around and group your data, pivot your table, etc as we sort of need to see the shape of your data and how it's stored.

## Re: Perform calculation on customers that were active in both periods

Apparently you can't attach files in these threads, so here's a copy of some sample data.  For account_status_id, 1 is active and 2 is cancelled.  Also, as you can tell from the data, this is updated on a weekly basis.  I hope this helps.  Thank you guys.

 account_weekly_log_date account_id account_status_id RCE State 1/20/2017 4 1 4.37 PA 1/20/2017 13 2 0.08 PA 1/20/2017 103 1 0.75 PA 1/20/2017 1003 1 1.11 PA 1/20/2017 10003 2 3.54 PA 1/20/2017 100003 1 0.86 NY 1/20/2017 100004 1 0.24 NY 1/20/2017 100005 1 1.81 NY 1/20/2017 100006 1 0.84 NY 1/20/2017 100007 2 6.82 NY 1/20/2017 100008 2 0.55 NY 1/13/2017 4 2 4.37 PA 1/13/2017 13 2 0.08 PA 1/13/2017 103 1 0.46 PA 1/13/2017 1003 1 1.11 PA 1/13/2017 10003 2 3.54 PA 1/13/2017 100003 1 0.85 NY 1/13/2017 100004 1 0.24 NY 1/13/2017 100005 1 1.73 NY 1/13/2017 100006 1 1.22 NY 1/13/2017 100007 1 6.82 NY 1/13/2017 100008 2 0.55 NY

## Re: Perform calculation on customers that were active in both periods

I think this does what you want.  See attachment.

Here are my measures

```Total Selected Week =
CALCULATE (
SUM ( Data[RCE] ),
LASTDATE ( 'Calendar'[Date] ),
Data[account_status_id] = 1
)```

```Total Previous Week =
CALCULATE (
CALCULATE ( SUM ( Data[RCE] ), Data[account_status_id] = 1 ),
FILTER (
ALL ( 'Calendar' ),
'Calendar'[WeekID]
= MAX ( 'Calendar'[WeekID] ) - 1
)
)```
```Total Sales This Week Filtered =
VAR mytable =
FILTER (
SUMMARIZE ( Data, Data[account_id] ),
"This Week", [Total Selected Week],
"Last Week", [Total Previous Week]
),
[This Week] <> 0
&& [Last Week] <> 0
)
RETURN
CALCULATE ( SUM ( Data[RCE] ), mytable )```
```Total Sales Last Week Filtered =
VAR mytable =
FILTER (
SUMMARIZE ( Data, Data[account_id] ),
"This Week", [Total Selected Week],
"Last Week", [Total Previous Week]
),
[This Week] <> 0
&& [Last Week] <> 0
)
RETURN
CALCULATE (
SUM ( Data[RCE] ),
FILTER (
ALL ( 'Calendar' ),
'Calendar'[WeekID]
= MAX ( 'Calendar'[WeekID] ) - 1
),
mytable
)```

Here is the sample workbook.  https://www.dropbox.com/s/qv7m68txf16tcjk/both%20weeks.pbix?dl=1

I would love to hear from anyone that has a more efficient approach (for my learning).

* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

## Re: Perform calculation on customers that were active in both periods

@MattAllington thank you!  When I put it in a table and use account_id as the rows, it seems to work fine.  However, I have to filter down to a handful of account_id's for faster load because there are so many account_id's.  But what if I want to visualize this with a line chart?  In other words, I want to see a cumulative trend of RCE's based on accounts that were active current week and previous week.  I'm having trouble doing this.

## Re: Perform calculation on customers that were active in both periods

Yes performance will always be a problem with this formula.  As I said, there may be a more efficient approach, but it would need a DB ninja to help with that.  Someone like @Oxenskiold

Sorry, I am not clear what this line chart would look like.  Do you mean that you would put date on the X axis and then want to see the line grow each week?

* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Community Champion

## Re: Perform calculation on customers that were active in both periods

Following these affirmation

* You want Weekly the sum Of RCE only for ID where are active this week and LastWeek

1. Create a new column

```Status_LastWeek =
CALCULATE (
VALUES ( Table1[account_status_id] ),
FILTER (
ALL ( Table1 ),
Table1[account_weekly_log_date]
= EARLIER ( Table1[account_weekly_log_date] ) - 7
&& Table1[account_id] = EARLIER ( Table1[account_id] )
)
)```

2. Create a measure to Sum RCE where Status_id =1 and Status_LastWeek = 1

```TotalRCEActiveCurrentandLastWeek =
CALCULATE (
SUM ( Table1[RCE] ),
FILTER ( Table1, Table1[account_status_id] = 1 && Table1[Status_LastWeek] = 1 )
)```

Lima - Peru

Announcements

#### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

#### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

#### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

#### Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

#### Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors