Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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. Ho...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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:
     
     
Anonymous's avatar
Anonymous
Not applicable
7 years ago

 

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's avatar
Anonymous
Not applicable
7 years ago

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: