Previous Week Revenue and Counts

[mcornejo_1]

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

[v-yuta-msft]

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

[mcornejo_1]

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