Variance analysis model with dynamic period in DAX

In this post i want to propose a solution to implement the variance analysis. In this particular exercise I want to have two pivot tables where one show  the variance analysis for product and one for the customer. Also selected period can change and so the quantity are recalculated on the selected period .

 Customer A

cusomer1

 

Customer B

cusomer2

 

Customer C

cusomer3

 

Total for product

cusomer4

 

the my task will be to create two pivot table in a spreadsheet . In left pivot table i want to have the variance analysis by product ( Last picture above ) and right want to have a pivot table with details of a single product for the customer to choose the right (see images of the customer A, B , C ). Below there is the image of the model with relationship. Calendar is the time table , M_Cus is the customer table , M_Prod is the product table and Sales is the sales table.

SchemaER

Calculate dynamic quantity for current year

CY_Qt_Mm :=
CALCULATE (
    SUM ( Sales[Qty] );
    DATESBETWEEN ( Calendar[Date]; [From]; [To] )
)

Calculate dynamic quantity for previous year

PY_Qt_Mm :=
CALCULATE (
    SUM ( Sales[Qty] );
    DATEADD (
        DATESBETWEEN ( Calendar[Date]; [From]; [To] );
        -1;
        YEAR
    )
)

Calculate MIX% current year

CY_Mix% :=
CALCULATE ( [CY_Qt_Mm]; VALUES ( M_Prod ) )
    / CALCULATE (
        SUMX ( VALUES ( M_Prod ); [CY_Qt_Mm] );
        ALL ( M_Prod )
    )

Calculate MIX% previous year

PY_Mix% :=
CALCULATE ( [PY_Qt_Mm]; VALUES ( M_Prod ) )
    / CALCULATE (
        SUMX ( VALUES ( M_Prod ); [PY_Qt_Mm] );
        ALL ( M_Prod )
    )

Calculate amount current year

CY_Amount :=
CALCULATE (
    SUM ( Sales[Amount] );
    DATESBETWEEN ( Calendar[Date]; [From]; [To] )
)

Calculate amount prevoius year

PY_Amount :=
CALCULATE (
    SUM ( Sales[Amount] );
    DATEADD (
        DATESBETWEEN ( Calendario[Data]; [Da]; [A] );
        -1;
        YEAR
    )
)

Calculate price current year

CY_Price :=
DIVIDE ( [CY_Amount]; [CY_Qt_Mm]; 0 )

Calculate price previous year

PY_Price :=
DIVIDE ( [PY_Amount]; [PY_Qt_Mm]; 0 )

Calculate MIX CY

MixAP:=[PY_Mix%]
    * CALCULATE (
        SUMX ( VALUES ( M_Prod ); [CY_Qt_Mm] );
        ALL ( M_Prod )
    )

Calculate DeltaTot

DeltaTot:=[CY_Amount] - [PY_Amount]

Calculate Replacement

Replacement :=
SUMX (
    SUMMARIZE ( Sales; M_Prod[CodProd] );
    SUMX (
        SUMMARIZE ( Sales; M_Cus[CodCli] );
        CALCULATE (
            IF ( [PY_Qt_Mm] = 0 || [CY_Qt_Mm] = 0; [DeltaTot]; 0 )
        )
    )
)

Calculate Price variance

PriceVariance :=
SUMX (
    SUMMARIZE ( Sales; M_Prod[CodProd] );
    SUMX (
        SUMMARIZE ( Sales; M_Cus[CodCli] );
        CALCULATE (
            IF (
                [PY_Qt_Mm] = 0
                    || [CY_Qt_Mm] = 0;
                00;
                ( [CY_Price] - [PY_Price] )
                    * [CY_Qt_Mm]
            )
        )
    )
)

Calculate Delta totale 2 (volume variance)

VolumeVariance :=
SUMX (
    SUMMARIZE ( Sales; M_Prod[CodProd] );
    SUMX (
        SUMMARIZE ( Sales; M_Cus[CodCli] );
        CALCULATE (
            IF (
                [PY_Qt_Mm] = 0
                    || [CY_Qt_Mm] = 0;
                0;
                ( [CY_Qt_Mm] - [PY_Qt_Mm] )
                    * [PY_Price]
            )
        )
    )
)

Calculate delta volume variance

VolumeVariance1 :=
SUMX (
    SUMMARIZE ( Sales; M_Prod[CodProd] );
    SUMX (
        SUMMARIZE ( Sales; M_Cus[CodCli] );
        CALCULATE (
            IF (
                [PY_Qt_Mm] = 0
                    || [CY_Qt_Mm] = 0;
                0;
                ( [MixAP] - [PY_Qt_Mm] )
                    * [PY_Price]
            )
        )
    )
)

Calculate delta mix volumne variance

MixVolumeVariance :=
SUMX (
    SUMMARIZE ( Sales; M_Prod[CodProd] );
    SUMX (
        SUMMARIZE ( Sales; M_Cus[CodCli] );
        CALCULATE (
            IF (
                [PY_Qt_Mm] = 0
                    || [CY_Qt_Mm] = 0;
                0;
                ( [CY_Qt_Mm] - [MixAP] )
                    * [PY_Price]
            )
        )
    )
)

Below show that when choose the category Disp in the right side in the second pivot table show the detail of variance for customer. At this link  the excel ( 4MB) file is available.

 DemoFinale

 

 

Comments are closed.

Switch to our mobile site