Forum Discussion
Previous Week Revenue and Counts
I am having a problem understanding the various ways of trying to sum (in my case) revenue and counts for the previous week from the current date. I think at this point I am overthinking things and have the various techniques confused so I am unable to explain what I’ve tried. It seems that DAX is the best way to approach this, however, I am completely new to it and confused.
Based on some random data I created I am trying to create a Report table that sums up Revenue for the previous week and groups it into the different Modes. We use ISO Week so Monday is day 1 of the week and Sunday is day 7. My sample report could be run at any time from date 6/4/18 to 6/8/18 but would contain all data from 5/28/18 through 6/3/18.
Any help would be greatly appreciated. I hope I’ve explained clear enough what I am trying to do.
Report:
Mode | Revenue | Count |
Truckload | 525 | 18 |
Expedite | 400 | 10 |
LTL | 420 | 12 |
Small Package | 440 | 11 |
Underlying Data:
ID | Date | Revenue | Count | Mode |
1002 | 5/28/2018 | 65 | 3 | Truckload |
1003 | 5/29/2018 | 70 | 2 | Expedite |
1004 | 5/29/2018 | 75 | 2 | LTL |
1005 | 5/30/2018 | 80 | 4 | Small Package |
1006 | 5/30/2018 | 85 | 5 | Truckload |
1007 | 5/30/2018 | 90 | 1 | Expedite |
1008 | 5/30/2018 | 95 | 3 | LTL |
1009 | 5/31/2018 | 100 | 2 | Small Package |
1010 | 5/31/2018 | 105 | 2 | Truckload |
1011 | 6/1/2018 | 110 | 4 | Expedite |
1012 | 6/1/2018 | 115 | 5 | LTL |
1013 | 6/1/2018 | 120 | 1 | Small Package |
1014 | 6/1/2018 | 125 | 3 | Truckload |
1015 | 6/2/2018 | 130 | 2 | Expedite |
1016 | 6/2/2018 | 135 | 2 | LTL |
1017 | 6/3/2018 | 140 | 4 | Small Package |
1018 | 6/3/2018 | 145 | 5 | Truckload |
2 Replies
Hi mcornejo_1,
To achieve your requirement, you can create two measures using DAX like below:
Last Week Revenue = VAR Previous_Week_Start_Date = MAX ( Table1[Date] ) - 8 VAR Previous_Week_Last_Date = MAX ( Table1[Date] ) - 1 RETURN CALCULATE ( SUM ( Table1[Revenue] ), FILTER ( ALLEXCEPT ( Table1, Table1[Mode] ), Table1[Date] >= Previous_Week_Start_Date && Table1[Date] <= Previous_Week_Last_Date ) ) Last Week Count = VAR Previous_Week_Start_Date = MAX ( Table1[Date] ) - 8 VAR Previous_Week_Last_Date = MAX ( Table1[Date] ) - 1 RETURN CALCULATE ( SUM ( Table1[Count] ), FILTER ( ALLEXCEPT ( Table1, Table1[Mode] ), Table1[Date] >= Previous_Week_Start_Date && Table1[Date] <= Previous_Week_Last_Date ) )PBIX here for your reference: https://www.dropbox.com/s/v8adrqgefj3fxoh/Previous%20Week%20Revenue%20and%20Counts.pbix?dl=0.
Regards,
Jimmy Tao
Thank you for responding Jimmy. I should have added that the entire query includes 2 years of data but that this is just one small piece of the report. I attempted to create the Revenue measure first, however, it is returning a much larger amount of revenue for last week than it should.
When I try to look at detail it is returning data from last year through this year and showing duplicate revenue data for many shipments. I am not sure how it is summing up the Revenue because these numbers don't match the actual data at all. I included some actual data to show the unique values. Just to be clear the "Actual Revenue" column I manually added to the table below. Thank you.
Mode Last Week Revenue Actual Revenue RevenueDate ID LTL 103.13 177.86 1/3/2017 70210686HB LTL 49240.07 133.93 1/4/2017 70210760HB LTL 219516.63 306.78 1/13/2017 70210767HB LTL 165201.81 449.35 1/9/2017 70210773HB LTL 103.13 226.07 1/3/2017 70210849HB LTL 103.13 186.5 1/3/2017 70210910HB LTL 103.13 1651.73 1/3/2017 70210920HB LTL 165201.81 706.02 1/9/2017 70210923HB LTL 92144.29 1128.89 1/5/2017 70210931HB LTL 49240.07 187.93 1/4/2017 70210998HB LTL 132227.15 403.53 1/6/2017 70211000HB LTL 49240.07 384.05 1/4/2017 70211009HB LTL 203909.17 236.4 1/10/2017 70211014HB LTL 49240.07 153.25 1/4/2017 70211024HB LTL 218890.03 441.56 1/12/2017 70211200HB LTL 49240.07 231 1/4/2017 70211298HB LTL 103.13 168.22 1/3/2017 70211387HB LTL 103.13 266.46 1/3/2017 70211411HB LTL 92144.29 246.87 1/5/2017 70211508HB LTL 49240.07 650.68 1/4/2017 70211708HB LTL 49240.07 553.75 1/4/2017 70211709HB LTL 49240.07 204.77 1/4/2017 70211718HB LTL 49240.07 327.3 1/4/2017 70211722HB LTL 49240.07 285.5 1/4/2017 70211738HB LTL 103.13 512.53 1/3/2017 70211750HB LTL 49240.07 291.06 1/4/2017 70211752HB
