URGENT: Variance Report

[Anonymous]

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

[MFelix]

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]

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]

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

[MFelix]

Hi @Anonymous ,

Sorry but I'm still not getting how you are filtering the information, I'm not abble to filter out the information in the same way you have it and I have a lot more lines than you on the calculations.

The formulas you present on the screen shot are correct to what I can see so what you need to do is a calculation by the item and picking up both of the CURRENT and AVERAGE  prices.

Should be something similar to: 

SUMX(VALUES(Table[Item], [Current] - [Average])

This would give you the difference between the two values, this is just a guessing and may need some changes since has I said I'm not understanding the filter context of your visualization.

Regarding the second part the COUNTROWS syntax is made over a table so you need to change the way you have your formula:

MEASURE =
CALCULATE (
    COUNTROWS ( FILTER ( 'Invoice Variance', [Current KEY] = [Previous KEY] ) )
)

[Anonymous]

Hi Miguel,

Below is the link to the new file that uses the previous screenshot:

https://drive.google.com/file/d/1MCzeCEaGFjLriaQExEdD4TV_0RGH5J7d/view?usp=sharing

I REALLY appreciate your help with this as this report as been ongoing for a year. So you're right, I think I have the corrrect calculations to get the current average price, current volume, and previous average price.

My issue is getting the variance calculation which is

(Current Average Price - Previous Average Price) * Current Volume

-- But only for rows where the license key are the same. So in the table below, I only need the rows where the keys match. Blue vs Red

Here is an Excel file with the correct result in green:

https://drive.google.com/file/d/1y4f0n2O4SLsKdMM-H5hi65gXT-0xKvAF/view?usp=sharing

You have no idea how much your help means to me at this stage. Im ready to not be up all night trying different things.

[MFelix]

Hi @Anonymous ,

This is a question about context, if you take out the date from your setup and add the following measure:

Variance = ([Current Avg Price] - [Previous Avg Price]) * [Current Volume]

You get the result below:

Has you can see the value highlited is according to your excel file, the question on this case is the total valuesif you want then calculated in the same way or has a SUM of all the previous values.

Check PBIX file attach and get back to me to what is correct or incorrect in the calculation.

[Anonymous]

The calculation is correct but I need the date included because they are trying to get a bar graph with 4 other variables (that customer start & stop thing mentioned before). So in this ecample, while that 5.57 is correct, the other rows should be 0 since they didnt have a matching value. This is where I was trying to use Count Rows maybe but I couldnt figure out how to do it correctly:

[MFelix · Accepted solution]

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.