Merging Two Columns With The Same Date Range but Different Years?

[CEllinger]

I have a table that is Sales by date. It looks like this:

Day # of Year	Date	Month	Year	Purchaser	Orders
1	1/1/2019	1	2019	Dan	1
2	1/2/2019	1	2019	Dan	2
3	1/3/2019	1	2019	Dan	3
1	1/1/2020	1	2020	Dan	4
2	1/2/2020	1	2020	Dan	5
3	1/3/2020	1	2020

My Measures are:

2019 Totals = Calculate(Sum(Sales[Order]),Sales[Year]=2019

2020 Totals = Calculate(Sum(Sales[Order]),Sales[Year]=2020

When I plot these measures I get the values distributed correctly.

However, 2020 (obviously) is missing date entries. I want to take the 2019 Total for each day and apply it to the dates missing in 2020. Using my previous example, the data would look like this:

Day # of Year	Date	Month	Year	Purchaser	Orders
1	1/1/2019	1	2019	Dan	1
2	1/2/2019	1	2019	Dan	2
3	1/3/2019	1	2019	Dan	3
1	1/1/2020	1	2020	Dan	4
2	1/2/2020	1	2020	Dan	5
3	1/3/2020	1	2020	Dan	3

How do I strip just the Year off of my calculations so this works?

[v-juanli-msft · Accepted solution]

Hi @CEllinger 

When you import data into Power BI first time, please add an index column, then create calcualted columns as below:

Purchaser2 =
IF (
    [Purchaser]
        = BLANK (),
    CALCULATE (
        MAX ( 'Table'[Purchaser] ),
        FILTER (
            'Table',
            'Table'[Index]
                = EARLIER ( 'Table'[Index] ) - 1
        )
    ),
    [Purchaser]
)

orders2 =
IF (
    [Orders]
        = BLANK (),
    CALCULATE (
        MAX ( 'Table'[Orders] ),
        FILTER (
            'Table',
            'Table'[Month]
                = EARLIER ( 'Table'[Month] )
                && 'Table'[Day]
                    = EARLIER ( 'Table'[Day] )
                && 'Table'[Year]
                    = EARLIER ( 'Table'[Year] ) - 1
        )
    ),
    [Orders]
)

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.