Running difference

[Anonymous]

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])

        )

    )

)

Based on: https://community.powerbi.com/t5/Desktop/Difference-on-running-total/m-p/397677#M181497

I have attached a snippet of the output but I am getting the samve result in the RunDiff column as the Value column

[v-yuta-msft]

@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]

@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 · Accepted solution]

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: