PowerBI: how do you calculate the difference betwe

2019-07-15 10:58发布

I have a matrix defined as the values : Pivot table with Multiple-Level Columns

I wanted to run a difference between the values that is seen on each date from Baseline and Scenario1 (it can be more than 2) So the columns are defined as : (1) Date (2) Type

So the difference is from Baseline for each Date much as what we do in Calculation - Difference in Excel or Tablaeu

Does anybody have any materials where I can follow through? I have the following DAX but it does not give me the result I expect :

Diff = 
VAR currIndexDate = MIN (Date [TotalRundate]) 
VAR currIndexType = "Scenario1" 

var currVolume = 
CALCULATE ( SUM (Data[TotalVolume]), 
      FILTER( ALLSELECTED (Data) , Data[TotalRunDate] = currIndexDate && Date[Type] = currIndexType ),
      VALUES (Data[Type]), VALUES (Data[TotalRunDate])
)
var prevVolume = 
CALCULATE ( SUM (Data[TotalVolume]), 
      FILTER( ALLSELECTED (Data) , Data[TotalRunDate] = currIndexDate && Date[Type] <> currIndexType ),
      VALUES (Data[Type]), VALUES (Data[TotalRunDate])
)
RETURN 
IF (prevVolume <> BLANK , currVolume - prevVolume, currVolume)

Here is the result that I am getting which sums everything and not within the same grouping of Data[Type] Data[TotalRunDate]

Output Received

What I want to accomplish is this type: (which is the Difference Calculation in Excel)

Excel Results

0条回答
登录 后发表回答