Period to Date Sales This Year versus Period to Date Sales Last Year?

[Quinnie2017]

I am trying to solve for sales period to date this year compared to period to date last year, using a Period slicer.

(sample data included at end of post)

Slicer = 2021 Period 2

Desired Result:

Today is the first day 2021 Period 2 and sales are $100

First day of the 2020 Period 2 sales were $100

Then Period To Date sales change this year - last year = $0

Current Result (Bad!)

Today is the first day of the 2021 Period 2 and sales are $100

Total 2020 Period 2 sales last year last = $2,800

Sale change versus last year = -$2,700

The problem seems to be that my slicer wants to use all days from 2020 Period 2, when I only want it to use the first day of 2020 Period 2.

Stated another way, I want a DAX statement that says:

Give me this year sales Period to Date and last year sales Period to Date and calculate the change.

Sample data below.  Any help would be appreciated!

Business Date	Year	Period	Week	Day	Year_Period	Sales
1/28/2020	2020	2	1	1	2020P2	100
1/29/2020	2020	2	1	2	2020P2	100
1/30/2020	2020	2	1	3	2020P2	100
1/31/2020	2020	2	1	4	2020P2	100
2/1/2020	2020	2	1	5	2020P2	100
2/2/2020	2020	2	1	6	2020P2	100
2/3/2020	2020	2	1	7	2020P2	100
2/4/2020	2020	2	2	8	2020P2	100
2/5/2020	2020	2	2	9	2020P2	100
2/6/2020	2020	2	2	10	2020P2	100
2/7/2020	2020	2	2	11	2020P2	100
2/8/2020	2020	2	2	12	2020P2	100
2/9/2020	2020	2	2	13	2020P2	100
2/10/2020	2020	2	2	14	2020P2	100
2/11/2020	2020	2	3	15	2020P2	100
2/12/2020	2020	2	3	16	2020P2	100
2/13/2020	2020	2	3	17	2020P2	100
2/14/2020	2020	2	3	18	2020P2	100
2/15/2020	2020	2	3	19	2020P2	100
2/16/2020	2020	2	3	20	2020P2	100
2/17/2020	2020	2	3	21	2020P2	100
2/18/2020	2020	2	4	22	2020P2	100
2/19/2020	2020	2	4	23	2020P2	100
2/20/2020	2020	2	4	24	2020P2	100
2/21/2020	2020	2	4	25	2020P2	100
2/22/2020	2020	2	4	26	2020P2	100
2/23/2020	2020	2	4	27	2020P2	100
2/24/2020	2020	2	4	28	2020P2	100
1/26/2021	2021	2	1	1	2021P2	100
1/27/2021	2021	2	1	2	2021P2
1/28/2021	2021	2	1	3	2021P2
1/29/2021	2021	2	1	4	2021P2
1/30/2021	2021	2	1	5	2021P2
1/31/2021	2021	2	1	6	2021P2
2/1/2021	2021	2	1	7	2021P2
2/2/2021	2021	2	2	8	2021P2
2/3/2021	2021	2	2	9	2021P2
2/4/2021	2021	2	2	10	2021P2
2/5/2021	2021	2	2	11	2021P2
2/6/2021	2021	2	2	12	2021P2
2/7/2021	2021	2	2	13	2021P2
2/8/2021	2021	2	2	14	2021P2
2/9/2021	2021	2	3	15	2021P2
2/10/2021	2021	2	3	16	2021P2
2/11/2021	2021	2	3	17	2021P2
2/12/2021	2021	2	3	18	2021P2
2/13/2021	2021	2	3	19	2021P2
2/14/2021	2021	2	3	20	2021P2
2/15/2021	2021	2	3	21	2021P2
2/16/2021	2021	2	4	22	2021P2
2/17/2021	2021	2	4	23	2021P2
2/18/2021	2021	2	4	24	2021P2
2/19/2021	2021	2	4	25	2021P2
2/20/2021	2021	2	4	26	2021P2
2/21/2021	2021	2	4	27	2021P2
2/22/2021	2021	2	4	28	2021P2

[edhans · Accepted solution]

This isn't the final solution, but will get you pointed in the right direction. You need to have a true date table. You have commingled a date dim (dimension) table and a fact table. I've added a date table to the model from here. 

The measure to get YTD sales is:

YTD Sales = 
VAR varCurrentDate = MAX('Date'[Date])
VAR varCurrentYear = MAX('Date'[Year])
VAR varCumulativeSales = 
    CALCULATE(
        SUM('Table'[Sales]),
        FILTER(
            ALL('Date'[Date],'Date'[Year]),
            'Date'[Year] = varCurrentYear
                && 'Date'[Date] <= varCurrentDate
        )
    )
RETURN
    varCumulativeSales

The measure to get prior YTD sales then is pretty much the same but rolled back 1 year.

PYTD Sales = 
VAR varCurrentDate = MAX('Date'[Date]) - 365
VAR varCurrentYear = MAX('Date'[Year]) - 1
VAR varCumulativeSales = 
    CALCULATE(
        SUM('Table'[Sales]),
        FILTER(
            ALL('Date'[Date],'Date'[Year]),
            'Date'[Year] = varCurrentYear
                && 'Date'[Date] <= varCurrentDate
        )
    )
RETURN
    varCumulativeSales

You can then add another measure that is just [YTD Sales] - [PYTD Sales]

Here is my file. This isn't perfect. You have a lot of missing data, but I get this is a sample. You'll note I hid values in the future using the IsFuture field of my date table. You may want to take a different approach if you don't have sales through today available.

Here is my PBIX file.

[Quinnie2017]

Thank you very much - and for being forgiving about incomplete data, including the date table (lesson learned).

I was able to recreate your measures and they did not error out.

Just so I understand, if I now put a slicer with year and date, these measures will calculate based upon the slicer selection?

Thanks again.

[edhans]

They should, yes. The slicer will filter those visuals which will in turn filter the data within it for the relevant date ranges.