cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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  
PersonReport CategoryDescriptionRowTypeFirstSecondThirdFirstSecondThird
BillTotal Pre-TaxIncomeIL00200100010001200
   NY0135-125240375250
  Total Pre-TaxTotals01125-11252501375250
 Total TaxTotal TaxTotals0-147-54-67.5-61
  Wage taxIL00-6-30-30-36
   NY0-1413-24-37.5-25
TedTotal Pre-TaxIncomeIL0100-12510020075
   NY0200-200150350150
  Total Pre-TaxTotals0300-300250550250
 Total TaxTotal TaxTotals0-2324-18-41-17.25
  Wage taxIL0-34-3-6-2.25
   NY0-2020-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 Tax Example.png

 

 

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:
Tax Example 4.png
 
 

View solution in original post

3 REPLIES 3
v-yuta-msft
Community Support
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

 

 

Tax Example 2.png

 

 

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:
Tax Example 4.png
 
 

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors