• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dynamic Deltas between DataSetID

Hi. I have this measure built that appears to be working fine until a DataSetID does not have values with the Line Description on my matrix. Essentially, I am trying to get deltas between the current dataset and the one prior (BUDGET27B = BUDGET27B - BUDGET27A and so on). I have an ID tag that puts them in order and makes them easy to reference. In the example picture, Inpatient Revenue went to zero in BUDGET27N (which will return no data from our financial system), however, it shows increases of $8.8m there and going forward.

I have been trying to pound through this and I am stuck.

zz_Bridge =
VAR CurrentIteration = MAX (Data[Iteration Order ID])
VAR PriorIteration = CurrentIteration-1
RETURN
SWITCH(TRUE(),
MAX('PL Format'[Order ID]) =4, "",
MAX('PL Format'[Order ID]) =8, "",
MAX('PL Format'[Order ID]) =13, "",
MAX('PL Format'[Order ID]) =15, "",
MAX('PL Format'[Order ID]) =17, "",
MAX('PL Format'[Order ID]) =29, "",
MAX('PL Format'[Order ID]) =31, "",
MAX('PL Format'[Order ID]) =33, "",
MAX('PL Format'[Order ID]) =35, "",
MAX('PL Format'[Order ID]) =37, "",
MAX('PL Format'[Order ID]) =39, "",
MAX('PL Format'[Order ID]) =41, "",
CALCULATE (
[Amt],
REMOVEFILTERS(Data[DataSetID]),
Data[Iteration Order ID] = CurrentIteration) = "" &&
CALCULATE (
[Amt],
REMOVEFILTERS(Data[DataSetID]),
Data[Iteration Order ID] = PriorIteration) = "",
0,
CALCULATE (
[Amt],
REMOVEFILTERS(Data[DataSetID]),
Data[Iteration Order ID] = CurrentIteration) = "",
0 - CALCULATE (
[Amt],
REMOVEFILTERS(Data[DataSetID]),
Data[Iteration Order ID] = PriorIteration),
CALCULATE (
[Amt],
REMOVEFILTERS(Data[DataSetID]),
Data[Iteration Order ID] = PriorIteration) = "",
CALCULATE (
[Amt],
REMOVEFILTERS(Data[DataSetID]),
Data[Iteration Order ID] = CurrentIteration) - 0,
CALCULATE (
[Amt],
REMOVEFILTERS(Data[DataSetID]),
Data[Iteration Order ID] = CurrentIteration)-
CALCULATE (
[Amt],
REMOVEFILTERS(Data[DataSetID]),
Data[Iteration Order ID] = PriorIteration))

1781108198891.png
 
Back
Top