cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
biz_wiz
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:

 

CustomerIDDateAccumulated costs
131.3.2019100
130.4.2019100
131.5.2019

150

1...

...

231.3.2019100
230.4.2019200
231.5.2019200
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

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

View solution in original post

3 REPLIES 3
TeigeGao
Solution Sage
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:

PBIDesktop_oQ880Qg1S6.png

Best Regards,

Teige

biz_wiz
Frequent Visitor

Hi @TeigeGao ,

 

Thanks for the help!

 

One issue with your solution:

 

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.

 

CustomerIDDateAccumulated costsLocation 
131.3.2019100A
130.4.2019100A
131.5.2019150A
231.3.2019100B
230.4.2019200B
231.5.2019200B
331.3.201950C
330.4.201950C
331.5.201950C

 

columns_allocation.PNG

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

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors