cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
iDataDrew Advocate IV
Advocate IV

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.
ElliotP Post Prodigy
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.

 

 

iDataDrew Advocate IV
Advocate IV

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_dateaccount_idaccount_status_idRCEState
1/20/2017414.37PA
1/20/20171320.08PA
1/20/201710310.75PA
1/20/2017100311.11PA
1/20/20171000323.54PA
1/20/201710000310.86NY
1/20/201710000410.24NY
1/20/201710000511.81NY
1/20/201710000610.84NY
1/20/201710000726.82NY
1/20/201710000820.55NY
1/13/2017424.37PA
1/13/20171320.08PA
1/13/201710310.46PA
1/13/2017100311.11PA
1/13/20171000323.54PA
1/13/201710000310.85NY
1/13/201710000410.24NY
1/13/201710000511.73NY
1/13/201710000611.22NY
1/13/201710000716.82NY
1/13/201710000820.55NY

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

I think this does what you want.  See attachment.

both.png


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 (
        ADDCOLUMNS (
            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 (
        ADDCOLUMNS (
            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.
iDataDrew Advocate IV
Advocate IV

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.
Vvelarde Community Champion
Community Champion

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

hi @iDataDrew

 

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

Helpful resources

Announcements
New Ranks Launched March 24th!

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!

‘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

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 Microsoft Power Platform event series.

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

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