cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## Running difference

This is a running difference problem.  I tried to take the learnings I saw in another post but was unsuccessful.

What I want to get to is this rough presentation below.  I have two questions:

1. How to I get my column formula to work?

2. If I start having other filters (e.g., I might want to put in a filter/slicer and remove the RowType called "Totals" so how can I do something similar as a measure formula vs a column formula?

 Tax Year 2018 Running Differences Person Report Category Description RowType First Second Third First Second Third Bill Total Pre-Tax Income IL 0 0 200 1000 1000 1200 NY 0 135 -125 240 375 250 Total Pre-Tax Totals 0 1125 -1125 250 1375 250 Total Tax Total Tax Totals 0 -14 7 -54 -67.5 -61 Wage tax IL 0 0 -6 -30 -30 -36 NY 0 -14 13 -24 -37.5 -25 Ted Total Pre-Tax Income IL 0 100 -125 100 200 75 NY 0 200 -200 150 350 150 Total Pre-Tax Totals 0 300 -300 250 550 250 Total Tax Total Tax Totals 0 -23 24 -18 -41 -17.25 Wage tax IL 0 -3 4 -3 -6 -2.25 NY 0 -20 20 -15 -35 -15

The Tax Year 2018 Running Differences is what I am trying to get to.  The numbers to the right of that is what I have as source data .

I tried writing the following RunDiff column formula:

RunDiff = Tax[Value]-LOOKUPVALUE(
Tax[Value],
Tax[Person],Tax[Person],
Tax[Report Category],Tax[Report Category],
Tax[Description],Tax[Description],
Tax[Tax Year],Tax[Tax Year],
Tax[Period], CALCULATE(
MAX(Tax[Period]),
FILTER (
ALLEXCEPT(Tax,Tax[Period]),
Tax[Period] < EARLIER(Tax[Period])
)
)
)
I have attached a snippet of the output but I am getting the samve result in the RunDiff column as the Value column

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Please ignore my previous post from a few minutes ago.   I found a data error so just for the people who are reading this post in the future, this DAX code did indeed work BUT!!! only when I added the RowType field to the previous suggested change. The successful DAX code now looks like this for the RunDiff column:

RunDiff =
Tax[Value]
- LOOKUPVALUE (
Tax[Value],
Tax[Person], Tax[Person],
Tax[Report Category], Tax[Report Category],
Tax[RowType],Tax[RowType],
Tax[Description], Tax[Description],
Tax[Tax Year], Tax[Tax Year],
Tax[Period], CALCULATE (
MAX ( Tax[Period] ),
FILTER ( Tax, Tax[Period] < EARLIER ( Tax[Period] ) )
)
)
If you fail to put in the RowType you will get the error message about too many values when a single value was expected.

Note that the column value "First" in Tax.Period returns the value and not the difference.  Since there is no prior column, that is ok and the slicer just removes it from the presentation anyway.  Here is what it looks like in the end where the differences from the values are presented:

3 REPLIES 3
Community Support

@Anonymous ,

To be general, the ALLEXCEPT() function will affect the filter(Remove filters on other columns). So you may modify your calculate column using DAX like pattern below and check if it can meet your requirement:

```RunDiff =
Tax[Value]
- LOOKUPVALUE (
Tax[Value],
Tax[Person], Tax[Person],
Tax[Report Category], Tax[Report Category],
Tax[Description], Tax[Description],
Tax[Tax Year], Tax[Tax Year],
Tax[Period], CALCULATE (
MAX ( Tax[Period] ),
FILTER ( Tax, Tax[Period] < EARLIER ( Tax[Period] ) )
)
)
```

Community Support Team _ Jimmy Tao

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

Anonymous
Not applicable

@v-yuta-msft   and _JimmyTao

First, let me tell you how satisfying it is that someone like you or the team responds.  I have been on plenty of communities from other companies where it isn't as responsive so thanks!!

I tried the solution but I am now getting a "A table of multiple values was supplied where a single value was expected."

So here are two more bits of information for you.  First, I noticed that I did not have "RowType" on the LOOKUPVALUE assuming that because there are 3 different values (IL, FL and Totals) that it was returning multiple values.  Second, I copied the CALCULATE portion of the code and placed it into another column : Test 2 = CALCULATE ( MAX ( Tax[Period] ), FILTER ( Tax, Tax[Period] < EARLIER ( Tax[Period] ) ) ).

I am assuming the blanks in Test 2 column are where CALCULATE is finding multiple values.  I am new to DAX so I may have this incorrect.

Here is the file if you want to play with it.

https://1drv.ms/f/s!AkCBPyPCuJKZtSy3xbIfwGESSGWf

Anonymous
Not applicable

Please ignore my previous post from a few minutes ago.   I found a data error so just for the people who are reading this post in the future, this DAX code did indeed work BUT!!! only when I added the RowType field to the previous suggested change. The successful DAX code now looks like this for the RunDiff column:

RunDiff =
Tax[Value]
- LOOKUPVALUE (
Tax[Value],
Tax[Person], Tax[Person],
Tax[Report Category], Tax[Report Category],
Tax[RowType],Tax[RowType],
Tax[Description], Tax[Description],
Tax[Tax Year], Tax[Tax Year],
Tax[Period], CALCULATE (
MAX ( Tax[Period] ),
FILTER ( Tax, Tax[Period] < EARLIER ( Tax[Period] ) )
)
)
If you fail to put in the RowType you will get the error message about too many values when a single value was expected.

Note that the column value "First" in Tax.Period returns the value and not the difference.  Since there is no prior column, that is ok and the slicer just removes it from the presentation anyway.  Here is what it looks like in the end where the differences from the values are presented:

Announcements