Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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.

 

 

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 an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

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

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 an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

@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.

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

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 an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.