cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
EA_EA Frequent Visitor
Frequent Visitor

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

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!

1 ACCEPTED SOLUTION

Accepted Solutions
fenchurchio Regular Visitor
Regular Visitor

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

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  

 

2019-07-21_10h31_12.png

 

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
        )
    )
1 REPLY 1
fenchurchio Regular Visitor
Regular Visitor

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

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  

 

2019-07-21_10h31_12.png

 

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
        )
    )

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 260 members 2,675 guests
Please welcome our newest community members: