Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
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 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |