Forum Discussion

CEllinger's avatar
CEllinger
Icon for Helper I rankHelper I
6 years ago
Solved

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?

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    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.

1 Reply

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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.