Forum Discussion

Migscruz's avatar
Migscruz
Icon for Helper I rankHelper I
5 years ago
Solved

How to create baseline sales

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 in...
Need Help
  • PaulDBrown's avatar
    PaulDBrown
    5 years ago

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

    model.JPG

    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)

    YM Index.JPG

    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:

    Sales.JPG

    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:

    Baseline Sales.JPG

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

PaulDBrown's avatar
PaulDBrown
Icon for Community Champion rankCommunity Champion
5 years ago

Migscruz 

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

  • Migscruz's avatar
    Migscruz
    Icon for Helper I rankHelper I
    5 years ago

    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's avatar
      PaulDBrown
      Icon for Community Champion rankCommunity Champion
      5 years ago

      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.

    • PaulDBrown's avatar
      PaulDBrown
      Icon for Community Champion rankCommunity Champion
      5 years ago

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

      model.JPG

      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)

      YM Index.JPG

      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:

      Sales.JPG

      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:

      Baseline Sales.JPG

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