cancel
Showing results for
Did you mean: Frequent Visitor

## Delta calculation for user defined period

Hi,

I have historized monthly data for customer accounts and I would need to build a measure that calculates the Delta for any period defined by the user.

My data looks like this:

 CustomerID Date Accumulated costs 1 31.3.2019 100 1 30.4.2019 100 1 31.5.2019 150 1 ... ... 2 31.3.2019 100 2 30.4.2019 200 2 31.5.2019 200 2 ... ...

From this data a bunch of measures are calculated, summing up Accumulated costs based on different attributes of the customer.

The desired functionality is that the user selects a start date and an end date in a slicer (based on attribute Date in data aboveand Power BI calculates the delta (change) in accumulated costs between those dates.

How could Power BI dynamically calculate the delta for any given period defined by the user?

Thanks for the help!

1 ACCEPTED SOLUTION  Solution Sage

Hi @biz_wiz ,

This is because I use the ALL() function, we can change it like below:

```Delta =
CALCULATE (
SUM ( Data[Accumulated costs] ),
FILTER (
ALL (Data[Date] ),
Data[Date] >= SELECTEDVALUE ( 'start date'[Date] )
&& Data[Date] <= SELECTEDVALUE ( 'end date'[Date] )
)
)```

Change the all (data) to ALL (Data[Date] )

Best Regards,

Teige

3 REPLIES 3  Solution Sage

Hi @biz_wiz ,

In your scenario, we can create two calendar tables for the start and end date slicer, then create the following measure:

```Delta =
CALCULATE (
SUM ( Data[Accumulated costs] ),
FILTER (
ALL ( Data ),
Data[Date] >= SELECTEDVALUE ( 'start date'[Date] )
&& Data[Date] <= SELECTEDVALUE ( 'end date'[Date] )
)
)```

The result will like below: Best Regards,

Teige Frequent Visitor

Hi @TeigeGao ,

Thanks for the help!

For some reason created Delta measure is not allocated correctly into columns in my Matrix visual, all columns show the total delta.

Delta should be allocated by attribute Location. See data below and screenshot of how delta is not allocated.

 CustomerID Date Accumulated costs Location 1 31.3.2019 100 A 1 30.4.2019 100 A 1 31.5.2019 150 A 2 31.3.2019 100 B 2 30.4.2019 200 B 2 31.5.2019 200 B 3 31.3.2019 50 C 3 30.4.2019 50 C 3 31.5.2019 50 C   Solution Sage

Hi @biz_wiz ,

This is because I use the ALL() function, we can change it like below:

```Delta =
CALCULATE (
SUM ( Data[Accumulated costs] ),
FILTER (
ALL (Data[Date] ),
Data[Date] >= SELECTEDVALUE ( 'start date'[Date] )
&& Data[Date] <= SELECTEDVALUE ( 'end date'[Date] )
)
)```

Change the all (data) to ALL (Data[Date] )

Best Regards,

Teige Announcements #### Welcome to the User Group Public Preview  