Struggling on DAX logic to calculate Average Daily Volume for Pre vs Post Analysis

[Rambo789]

Hello Community,

Its a very long thread but I am doing my best to be as elaborative as possible!

I'm working on creating Pre vs Post Analysis to understand the effect of an action on Customer.
I want compute Average Daily Volume for Pre vs Post Analysis. My data model is as follows.

These two tables are related through a composite key as Key = TransMonth + Service

 Notice that, the customers can be actioned during different months and based on that they will have more or less months for Post vs Pre comparison.

I have created a Parameter with slicer to select Pre-Post comparison period values between 1 and 12, which lets user set values for analysing performance based on 1 to 12 months of prior and post performance data. Prior[Prior Value] is set to 2. i.e. it will consider two months or Prior and Post data. For customers having only 1 month post data available, it should consider only 1 month of prior data.

I created a few calculated columns and two measures to address this by converting months to dates and finding the months difference between transaction months and Price Actioned months to compute Prior Quantity and Post Quantity as follows.

Three Calculated columns as follws

PriorActionPeriod = DATEDIFF(Transactions[Trans Date],Transactions[Action Date],MONTH)

PostActionPeriod = DATEDIFF(Transactions[Action Date],Transactions[Trans Date],MONTH)

Max Post Action Period= 
CALCULATE ( 
            MAX( Transactions[PostActionPeriod] )
            , ALLEXCEPT ( Transactions, Transactions[CustNbr] , Transactions[Service])
)

And Prior and Post Qty is computed through measures as Paramaters can be referenced only through measures (not calculated Columns)

Measure Prior Qty = 
CALCULATE (
    Sum([Quantity]),
    ISBLANK(Transactions[Action Date]) = FALSE(),
    FILTER(Transactions, Transactions[PriorActionPeriod] >= 0 && Transactions[PriorActionPeriod] <= Prior[Prior Value] - 1 
        && Transactions[PriorActionPeriod] < Transactions[Max Post Action Period] 
// To make sure Prior Doesnot consider more months than Post
      )
    )

Measure Post Qty = 
CALCULATE (
    Sum([Quantity]),
    ISBLANK(Transactions[Action Date]) = FALSE(),
    FILTER(Transactions, Transactions[PostActionPeriod] > 0 && Transactions[PostActionPeriod] <= Prior[Prior Value]
      )
    )

These two Prior and Post Qty are perfectly additive at Business Segment! Phew!
I struggle to compute Average Daily Volume in similar way as Operating Days are different for different services and Simply following logics do not add at overall and customer level

Measure Prior ADV= 
CALCULATE (
    (Sum([Quantity])/Sum([OpDays])),
    ISBLANK(Transactions[Action Date]) = FALSE(),
    FILTER(Transactions, Transactions[PriorActionPeriod] >= 0 && Transactions[PriorActionPeriod] <= Prior[Prior Value] - 1 
        && Transactions[PriorActionPeriod] < Transactions[Max Post Action Period] 
// To make sure Prior Doesnot consider more months than Post
      )
    )

Measure Post Qty = 
CALCULATE (
    (Sum([Quantity])/Sum([OpDays])),
    ISBLANK(Transactions[Action Date]) = FALSE(),
    FILTER(Transactions, Transactions[PostActionPeriod] > 0 && Transactions[PostActionPeriod] <= Prior[Prior Value]
      )
    )

I wish to achieve something like follows.

Appreciate your help for designing this DAX.!
Thanks, Rambo

[Rambo789]

Thank you @v-yangliu-msft for the solution! I think it solves the issue!
Can you please elaborate on the logic used for Total_Prior and Total_Post? I'm very new to these DAX formats.

Appreciate your help!

Best,

Rambo789

[v-yangliu-msft · Accepted solution]

Hi  @Rambo789 ,

Here are the steps you can follow：

1. Create measure.

Prior ADV1 =
SUMX(
    FILTER(ALL('Table'),
    'Table'[CustNbr]=MAX('Table'[CustNbr])&&'Table'[Service]=MAX('Table'[Service])),[Mesure Prior Qty Measure])

Prior ADV2 =
SUMX(
    FILTER(ALL('Table'),
    'Table'[CustNbr]=MAX('Table'[CustNbr])&&'Table'[Service]=MAX('Table'[Service])
    &&[Mesure Prior Qty Measure]<>BLANK()
    ),'Table'[OpDays])

Prior ADV3 =
DIVIDE(
   [Prior ADV1],[Prior ADV2])

Prior ADV4 = COUNTX(
    FILTER(ALL('Table'),
    'Table'[CustNbr]=MAX('Table'[CustNbr])&&'Table'[Service]=MAX('Table'[Service])),[CustNbr])

Prior ADV5 =
DIVIDE(
sumX(
    FILTER(ALL('Table'),
    'Table'[CustNbr]=MAX('Table'[CustNbr])),[Prior ADV3]),
    [Prior ADV4])

Post ADV1 =
SUMX(
    FILTER(ALL('Table'),
    'Table'[CustNbr]=MAX('Table'[CustNbr])&&'Table'[Service]=MAX('Table'[Service])),[Measure Post Qty measure])

Post ADV2 =
SUMX(
    FILTER(ALL('Table'),
    'Table'[CustNbr]=MAX('Table'[CustNbr])&&'Table'[Service]=MAX('Table'[Service])
    &&[Measure Post Qty measure]<>BLANK()  
    ),'Table'[OpDays])

Post ADV3 =
DIVIDE(
   [Post ADV1],[Post ADV2])

Post ADV4 =
COUNTX(
    FILTER(ALL('Table'),
    'Table'[CustNbr]=MAX('Table'[CustNbr])&&'Table'[Service]=MAX('Table'[Service])),[CustNbr])

Post ADV5 =
DIVIDE(
sumX(
    FILTER(ALL('Table'),
    'Table'[CustNbr]=MAX('Table'[CustNbr])),[Post ADV3]),
    [Post ADV4])

Total_Incorrect_Prior =
var _table=SUMMARIZE('Table','Table'[CustNbr],"_value",[Prior ADV5])
return
IF(HASONEVALUE('Table'[CustNbr]),[Prior ADV5],SUMX(_table,[_value]))

Total_Incorrect_Post =
var _table=SUMMARIZE('Table','Table'[CustNbr],"_value",[Post ADV5])
return
IF(HASONEVALUE('Table'[CustNbr]),[Post ADV5],SUMX(_table,[_value]))

Difference =
[Total_Incorrect_Prior] - [Total_Incorrect_Post]

Difference% =
DIVIDE(
   [Total_Incorrect_Post] - [Total_Incorrect_Prior]
   ,[Total_Incorrect_Prior])

2. Result:

If you need pbix, please click here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly