How to create baseline sales

[Migscruz]

Hi,

How can i create a baseline sales in PBI from a period y choose ?  for example i have my sales that is $ 1500 from december 1 to december 10 and i would like to know if this $ 1500 have an incremental vs my baseline sales. 

Thanks!!

[PaulDBrown]

@Migscruz 

What's the formula you want to use for baseline sales?

[Migscruz]

There @PaulDBrown ,

I have my date table and my sales table with the sales by day and product. I would like to have the average sales of the past 3 months using only the 15 first days of the month. For example:

- 15 first days of January = 100

- 15 first days of February = 200
- 15 first days of March = 300

Average of the 15 first days of January, February and March = 200

When i use the average formula it shows me the average of each day and product so the average that appears me is very low. It should grab the average of the total sales of each day and not by product.

Thanks!

[PaulDBrown · Accepted solution]

@Migscruz

I've tried to provide a solution based on what I think you're looking for, although I'm not quite sure what youmean when you say "average total sales every day and not by product."

However, to get started, I'll go through the process to calculate the average sales for the first 15 days of each month.

First the model (simple dataset):

In the calendar table, I added a YearMonth and Yearmonth Index column (the latter is a range in the YearMonth column that I'll use to filter the itching)

Now the measurements:

1) A simple sum of sales:

Sum of Sales = SUM('Sales Table'[Sales])

2) To calculate the sum of sales for the first 15 days of each month, I have created a cumulative sales measure for each month:

Cumulative Sum by month =
CALCULATE (
    [Sum of Sales],
    FILTER (
        ALL ( 'Calendar Table' ),
        'Calendar Table'[Date] <= MAX ( 'Calendar Table'[Date] )
            && 'Calendar Table'[YM Index] = SELECTEDVALUE ( 'Calendar Table'[YM Index] )
    )
)

3) Now we can isolate the sales value for the first 15 days of each month using:

Sales on day 15 =
CALCULATE (
    [Cumulative Sum by month],
    FILTER ( 'Calendar Table', DAY ( 'Calendar Table'[Date] ) = 15 )
)

What this table gives you:

To calculate baseline sales, use:

a) for sales including current month + 2 previous months:

Average Sales last 3 month =
IF (
    ISINSCOPE ( 'Calendar Table'[Month Name] ),
    AVERAGEX (
        FILTER (
            ALL ( 'Calendar Table' ),
            'Calendar Table'[YM Index]
                >= MAX ( 'Calendar Table'[YM Index] ) - 2
                && 'Calendar Table'[YM Index] <= MAX ( 'Calendar Table'[YM Index] )
        ),
        [Sales on day 15]
    )
)

b) for 3 previous months (excluding the current month):

Average Sales Previous 3 months =
IF (
    ISINSCOPE ( 'Calendar Table'[Month Name] ),
    AVERAGEX (
        FILTER (
            ALL ( 'Calendar Table' ),
            'Calendar Table'[YM Index]
                >= MAX ( 'Calendar Table'[YM Index] ) - 3
                && 'Calendar Table'[YM Index]
                    <= MAX ( 'Calendar Table'[YM Index] ) - 1
        ),
        [Sales on day 15]
    )
)

What this chart brings you:

If you can clarify the "product" point, we can polish these measures to meet your needs.

[PaulDBrown]

@Migscruz 

Thanks for that explanation. I'm still slightly unsure what you mean by "average of the total sales of each day and not by product". It might be easier if you created a mockup data in Excel to show exactly the calculation you are  looking for. 

It would also help if you could show the structure of the visual you wish to include the values in (or a table as an example). 

Thanks!

Paul.

[ryan_mayu]

@Migscruz 

maybe you can try to create a constant line.