Sum of previous day Sales by week

[Iamnvt]

hi,

I have a Calendar Table, and a Sales Table as below:

Date	Week
10/2/2018	40
10/3/2018	40
10/4/2018	40
10/5/2018	40
10/6/2018	40
10/7/2018	40
10/8/2018	41
10/9/2018	41
10/10/2018	41
10/11/2018	41
10/12/2018	41
10/13/2018	41
10/14/2018	41
10/15/2018	42
10/16/2018	42

Sales	Date
10	10/7/2018
12	10/7/2018
14	10/8/2018
15	10/9/2018
17	10/11/2018

I created a measure to calculate the previous day Sales:

=CALCULATE(sum(Sales[Sales]), FILTER(ALL('Calendar'), 'Calendar'[Date] = MAX('Calendar'[Date])+1))

how can I create a Sum of Previous Day Sales by week?

I want the result to be like:

Week	Previous Day Sales	Sum of Sales
40	36	22
41	32	46
Grand Total	68	68

Excel file: https://1drv.ms/x/s!Aps8poidQa5zk45I9gA6JQR-pMfzoA

Thanks,

[OwenAuger]

Hi @Iamnvt

From your description, you just want to shift the date values from the current filter context earlier by 1 day.

In that case, I would recommend this:

=
CALCULATE ( SUM ( Sales[Sales] ), DATEADD ( 'Calendar'[Date], -1, DAY ) )

Your sample measure & outputs were adding 1 to the max date which seems strange - you should be subtracting if anything.

I would expect output looking like this:

Week	Previous Day Sales	Sum of Sales
40		22
41	68	46
Grand Total	68	68

[Iamnvt]

Thanks,

it seems to work well!.

could you help to get me to understand the difference between the 2 measures?

=CALCULATE(sum(Sales[Sales]), FILTER(ALL('Calendar'), 'Calendar'[Date] = MAX('Calendar'[Date])+1))

=
CALCULATE ( SUM ( Sales[Sales] ), DATEADD ( 'Calendar'[Date], -1, DAY ) )

[OwenAuger]

That's good!

I think it's best looked at visually.

By the way, I'm assuming you meant to subtract 1 from the date in your original measure (not add 1).

Assuming you are filtering by week (not by day), the Calendar filters look like this:

In other words, DATEADD takes each date visible in the initial filter context and subtracts one day from each in this case, i.e. shifts the whole range one day earlier.

However the MAX(...) expression only gives you a single date (one less than the max date) for each 'cell' of your visual.

Regards,

Owen

[Iamnvt]

hi Owen,

this is an excellent explanation. 

I have a variation of this problem: instead of showing date, I have a day sequence of number: 1,2...,10.

Dateadd function cannot apply to the number. How could I address this issue?

DateWeek

1	40
2	40
3	40
4	40
5	40
6	40
7	41
8	41
9	41
10	41
11	41
12	41
13	41
14	42
15	42

Thank you very much

[OwenAuger]

If your "dates" are represented by an integer key instead, you could use a pattern like this.

I'm assuming 'Calendar'[Date] column is now an integer key.

Previous Day Sales =
VAR DateFilter =
    TREATAS (
        SELECTCOLUMNS ( VALUES ( 'Calendar'[Date] ), "Date-1", 'Calendar'[Date] - 1 ),
        'Calendar'[Date]
    )
RETURN
    CALCULATE ( SUM ( Sales[Sales] ), ALL ( 'Calendar' ), DateFilter )

This pattern should also work if 'Calendar'[Date] is actually a date.

It basically subtracts 1 from every visible Date value and applies that as a filter on the Date column, which I assume is similar to what DATEADD does behind the scenes.

Regards,

Owen 🙂

[Iamnvt]

hi Owen,

Do you have other ways of working on Excel? Treatas function is working on Power BI Desktop only.

Thanks again,

[OwenAuger · Accepted solution]

Sure, if you're using a version of DAX without TREATAS (such as PowerPivot) then you can use INTERSECT instead:

Previous Day Sales =
VAR DateFilter =
    SELECTCOLUMNS ( VALUES ( 'Calendar'[Date] ), "Date-1", 'Calendar'[Date] - 1 )
RETURN
    CALCULATE (
        SUM ( Sales[Sales] ),
        ALL ( 'Calendar' ),
        INTERSECT ( ALL ( 'Calendar'[Date] ), DateFilter )
    )

This page is a good reference:

https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/

Oh, if you're version of DAX doesn't have INTERSECT either (e.g. Excel 2013) then you would have to use an unwieldy expression using CONTAINS:

=
CALCULATE (
    SUM ( Sales[Sales] ),
    ALL ( 'Calendar' ),
    FILTER (
        ALL ( 'Calendar'[Date] ),
        CONTAINS (
            SUMMARIZE (
                ADDCOLUMNS ( VALUES ( 'Calendar'[Date] ), "Date-1", 'Calendar'[Date] - 1 ),
                [Date-1]
            ),
            [Date-1], 'Calendar'[Date]
        )
    )
)

Regards,

Owen 🙂

[Iamnvt]

Excellent! Thanks a lot.