Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

URGENT: Variance Report

I need help figuring out this calculation in Power BI. It's easy in Tableau with date parameters, but not sure how to do it in Power BI.

 

The calculation rules are below:

 

  1. Customer Start and Stop. This category has two sub-categories: (a) new Customer Starts and (b) Customer Stops.
    • Customer Starts. This is determined by when a customer signs up as a new account, a data field. Conceptually if a customer signs up/creates an account within the latest 12 months (or 365 days), it is a "new customer," and the volume variance falls into this bucket. By definition, there were some sales in the current period but zero sales in the prior period (if there were no sales in the current period nor sales for the previous period, then no variance exists). If it is not a new customer, the variance falls into another category below.
    • Customer Stops. This is for a customer who signed up (created an account) before the latest 12 month period, bought none of the item in the current period but did buy the item during the prior 12-month period.
    • Customer Starts and Stops are netted to produce the "Customer Start & Stop" variance.
  1. Part Start & Stop. This category has two sub-categories: 
    • Part Starts. When the customer signed up/created an account before the latest 12 months, purchased the item (volume) in the current period but had zero volume in the prior period. If there was volume for the previous period, then the variance falls into category 2(b) or (3) below.
    • Part Stops. When the customer signed up/created an account before the latest 12 months, the customer did not purchase the item (no volume) in the current period but bought some volume in the prior period. If there was volume in the current period and last period, then the variance falls into category (3) below.
    • Part Starts and Stops are netted to produce the "Part Start & Stop" variance.
  1. Price Variance = (Price current = Price Prior) x Volume current
  2. Volume Variance = (Volume current – volume prior) x Price prior

 

I tried doing two different tables, joined on the item key, because the variance calculations only look up the relevant customer-item transactions, but that breaks when calculating the customer start and stop and product start and stop because there is no data in the previous table.

 

The goal would be to use data tables to filter the data in the current and previous tables, but I can't seem to get them in betweens to work. Any help would be greatly appreciated.

 

This is an urgent need as the client needs it ASAP, and I have spent hours trying to find a solution in Power BI that replicates Tableau. 

 

https://drive.google.com/file/d/1EEpP1HkAQc5P0MkazTu7-J6raLocjEbl/view?usp=sharing

 

 

 

Need Help
  • MFelix's avatar
    MFelix
    5 years ago

    Hi Anonymous ,

     

    The question here is regarding the period selected in the Current Period and the granularity.

     

    What I did as to make changes to your two measures:

    Previous Avg Price =
CALCULATE (
    SUMX (
        SUMMARIZE (
            'Invoice Variance',
            'Invoice Variance'[item],
            "CurAvgPrice",
                DIVIDE (
                    SUM ( 'Invoice Variance'[ext_price] ),
                    SUM ( 'Invoice Variance'[volume] ),
                    0
                )
        ),
        [CurAvgPrice]
    ),
    FILTER (
        ALL ( 'Invoice Variance'[inv_date] ),
        'Invoice Variance'[inv_date]
            <= MAX ( 'Current'[Date] ) - 365
            && 'Invoice Variance'[inv_date]
                >= MIN ( 'Current'[Date] ) - 365
    )
)



Current Avg Price =
CALCULATE (
    SUMX (
        SUMMARIZE (
            'Invoice Variance',
            'Invoice Variance'[item],
            "CurAvgPrice",
                DIVIDE (
                    SUM ( 'Invoice Variance'[ext_price] ),
                    SUM ( 'Invoice Variance'[volume] ),
                    0
                )
        ),
        [CurAvgPrice]
    ),
    FILTER (
        ALL ( 'Invoice Variance'[inv_date] ),
        'Invoice Variance'[inv_date] <= MAX ( 'Current'[Date] )
            && 'Invoice Variance'[inv_date] >= MIN ( 'Current'[Date] )
    )
)

     

    Has you can see below the lines that you refer are picking up the values:

     

    All others have only a single value previous or current. This is impacted by a slicer with the dates of current with 2021 dates.

     

    What is happening is that since you were using the full date table when you compare the current date 2020 with previous year 2019 you are not getting values because your data starts in 2020, on the other and for the current values when you search for the current value on 2021 you are not getting values because your data is of 2020.. (not sure if I made it clear)

     

    Check PBIX file attach.

     

     

     

     

8 Replies

  • MFelix's avatar
    MFelix
    Super User
    5 years ago

    Hi Anonymous ,

     

    To what I can see from your model the two tabvle are exactly the same only difference are the last 3 columns that you calculated in dax.

     

    Believe that you can make the calculations based on a single table that then is filtered by the parameter dates that you created.

     

    Another question is about the previous period, you want to select that period from the slicer also, or is it the previous 12 months?

     

    Also not understanding the question about the two subcategories you want to put the customers in categories?

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      I can easily replicate the total avg price and variance but the issue is that they need it by ONLY items where the customer ordered the same product in the two time periods. For example, this is an item made in january

       

       

      I have the 4.04 and 3.84 values but the actual number is only for 1198 when it appears in the current date selection and the previous selection which is current -365

       

      I used the calculations to get the main number but do not know how to get the match:

      Current Avg Price =
      var CurLastDate = MAX('Current'[Date])
      var CurFirstDate = MIN('Current'[Date])
      return
      calculate (
          SUMX(
      SUMMARIZE('Invoice Variance','Invoice Variance'[item],
      "CurAvgPrice",DIVIDE(SUM('Invoice Variance'[ext_price]),SUM('Invoice Variance'[volume]),0)),[CurAvgPrice]),
          KEEPFILTERS( 'Invoice Variance'[inv_date] <= CurLastDate && 'Invoice Variance'[inv_date] >= CurFirstDate)
      )
      Current Volume =
      var CurLastDate = MAX('Current'[Date])
      var CurFirstDate = MIN('Current'[Date])
      return
      calculate (
          sum ( 'Invoice Variance'[volume] ),
          KEEPFILTERS( 'Invoice Variance'[inv_date] <= CurLastDate && 'Invoice Variance'[inv_date] >= CurFirstDate)
      )
       
      Previous Avg Price =
      var PrevLastDate = MAX('Current'[Date])-365
      var PrevFirstDate = MIN('Current'[Date])-365
      return
      calculate (
          SUMX(
      SUMMARIZE('Invoice Variance','Invoice Variance'[item],
      "PrevAvgPrice",DIVIDE(SUM('Invoice Variance'[ext_price]),SUM('Invoice Variance'[volume]),0)),[PrevAvgPrice]),
          KEEPFILTERS( 'Invoice Variance'[inv_date] <= PrevLastDate && 'Invoice Variance'[inv_date] >= PrevFirstDate)
      )
       
      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        How would I only get the values where the current key and the previous key match? (e.g. 1189-1)