Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
CEllinger
Helper I
Helper I

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

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

 

Day # of YearDateMonthYearPurchaserOrders
11/1/201912019Dan1
21/2/201912019Dan2
31/3/201912019Dan3
11/1/202012020Dan4
21/2/202012020Dan5
31/3/202012020  

 

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 YearDateMonthYearPurchaserOrders
11/1/201912019Dan1
21/2/201912019Dan2
31/3/201912019Dan3
11/1/202012020Dan4
21/2/202012020Dan5
31/3/202012020 Dan 3

 

 

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

1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

Hi @CEllinger 

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

Capture2.JPG

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.

View solution in original post

1 REPLY 1
v-juanli-msft
Community Support
Community Support

Hi @CEllinger 

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

Capture2.JPG

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.