Compare sales from current and last period, where items from LY is ignored.

[Anonymous]

Hi!

I am trying to create a single report with CY sales and LY sales using a national retail calendar, T-364 days.

The issue that I am facing is that there are several items that are no longer exist in the current year.  

Due to this, the LY sales excluded these items so the final LY sales amount is not accurate.

Is there a way to do this?  It has to be a single report.

For instance:

LY items: A, B, C, D, E

CY items: B, C, D, E, F

Item Name | CY Sales | LY Sales

B

C

D

E

F

Item A is excluded.  How do I put item A in the list so the LY sales amount is correct?

Thanks!

[Anonymous · Accepted solution]

In retail these are typically expressed as Like on Like calculations (LOL).

I have created a sample Power BI workbook with some calculations that demonstrate calculating which stores to include in the calculation.

Download: Power BI Workbook  

I have included calculations to calculate the Sales LOL PY. 

You will also need calculations to dislay this years values for the same stores, so you can actually do a comparison, so I have included Sales LOL as well.

LOL Sales PY = 
VAR varCurrentMonth =
    CALCULATE ( MAX ( 'Date'[Month] ) )
RETURN
    CALCULATE (
        [Sales],
        DATEADD ( 'Date'[Month], -1, YEAR ),
        FILTER (
            'Store',
            CALCULATE (
                DATEADD(FIRSTNONBLANK ( ALL('Date'[Month]), [Sales] ),1,YEAR)
            ) <= varCurrentMonth
        ),
        FILTER (
            'Store',
            CALCULATE (
                LASTNONBLANK( ALL('Date'[Month]), [Sales] )
            ) >= varCurrentMonth
        )
    )

LOL Sales = 
VAR varCurrentMonth =
    CALCULATE ( MAX ( 'Date'[Month] ) )
RETURN
    CALCULATE (
        [Sales],
        FILTER (
            'Store',
            CALCULATE (
                DATEADD(FIRSTNONBLANK ( ALL('Date'[Month]), [Sales] ),1,YEAR)
            ) <= varCurrentMonth
        ),
        FILTER (
            'Store',
            CALCULATE (
                LASTNONBLANK( ALL('Date'[Month]), [Sales] )
            ) >= varCurrentMonth
        )
    )