Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
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.
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 |
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 ( 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).
@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 ) )
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?
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |