Moving Difference

[mail2vjj]

Hello,

I have a table which looks similar to this WITHOUT the 'Change' column, which I want to calculate.

Date	Name	Holding	Change
01-01-18	A	10	10
01-01-18	B	10	10
01-01-18	C	10	10
02-02-18	A	20	10
02-02-18	B	30	20
02-02-18	C	40	30
03-01-18	A	10	-10
03-01-18	B	50	20
03-01-18	C	30	-10

I want to calculate the change in the holding of every particular name, compared to their last holding date.

For example: 

Name A has bought 10 quantity on 1st Jan, so the change is +10.

On 2nd Jan A's holding is 20, so the change is +10 compared to 1st Jan.

On 3rd Jan A's holding is 10, so the change is -10 compared to 2nd Jan.

So I am looking for a measure, that will give me the 'Change' column, i.e. the change in the holding compared to the previous available date.

Any help will be appreciated, if you still have any doubts, please do let me know.

Thank you,

Vishesh Jain

[Zubair_Muhammad · Accepted solution]

Hi @mail2vjj

Try this MEASURE

Measure =
VAR mypreviousdate =
    CALCULATE (
        MAX ( TableName[Date] ),
        FILTER (
            ALLEXCEPT ( TableName, TableName[Name] ),
            TableName[Date] < SELECTEDVALUE ( TableName[Date] )
        )
    )
RETURN
    SUM ( TableName[Holding] )
        - CALCULATE (
            SUM ( TableName[Holding] ),
            FILTER (
                ALLEXCEPT ( TableName, TableName[Name] ),
                TableName[Date] = mypreviousdate
            )
        )

[Zubair_Muhammad · Accepted solution]

@mail2vjj

As a calculated column, you can use this

Calculated Column =
VAR mypreviousdate =
    CALCULATE (
        MAX ( TableName[Date] ),
        FILTER (
            ALLEXCEPT ( TableName, TableName[Name] ),
            TableName[Date] < EARLIER ( TableName[Date] )
        )
    )
RETURN
    TableName[Holding]
        - CALCULATE (
            SUM ( TableName[Holding] ),
            FILTER (
                ALLEXCEPT ( TableName, TableName[Name] ),
                TableName[Date] = mypreviousdate
            )
        )

[mail2vjj]

@Zubair_Muhammad

First of all thank you for your prompt reply.

I had a question about the ALLEXCEPT you used in the formula, why is it necessary here and how is it helping getting the result.
I did try to remove it and the obviously the result was wrong.

So it would be of great help for me to understand if you could please explain it to me.

Thank you,

Vishesh Jain

[Zubair_Muhammad]

@mail2vjj

ALLEXCEPT removes all filters from the Table except from the Columns which are passed as arguments after the TableName

Because your MOVING difference has to take into account Name Column for computing the Difference, we must use

ALLEXCEPT ( TableName, TableName[Name] )

to return a table specific to that Name only

[mail2vjj]

@Zubair_Muhammad

Again thanks for the explanation.

I have another thing that I am trying, if you can help me out with that as well.

https://community.powerbi.com/t5/Desktop/Opposite-Intersection/m-p/339759#M152077

This is the link to the problem.

I have reached a partial solution, but it is only working if I choose a date on the slicer.

For result one this is the forumla I used, based on your FILTER concept.

Result 1 = EXCEPT(
CALCULATETABLE(VALUES(Sheet1[Name]), FILTER(ALLEXCEPT(Sheet1, Sheet1[Name]), Sheet1[Date] = SELECTEDVALUE(Sheet1[Date]))),
CALCULATETABLE(VALUES(Sheet1[Name]), FILTER(ALLEXCEPT(Sheet1, Sheet1[Name]), Sheet1[Date] < SELECTEDVALUE(Sheet1[Date])))
)

For Result 2 I just filpped the 'less than' and 'equal to' signs to change the tables.

Result 2 = EXCEPT(
CALCULATETABLE(VALUES(Sheet1[Name]), FILTER(ALLEXCEPT(Sheet1, Sheet1[Name]), Sheet1[Date] < SELECTEDVALUE(Sheet1[Date]))),
CALCULATETABLE(VALUES(Sheet1[Name]), FILTER(ALLEXCEPT(Sheet1, Sheet1[Name]), Sheet1[Date] = SELECTEDVALUE(Sheet1[Date])))
)

However the problem I am facing here is that, since Name C has occured previously, it is not giving me the desired result, when is reoccurs on 6th Jan 2018 after not showing up on 5th Jan 2018.

Also, probably since I am using SELECTEDVALUES, the Matrix visual gives me an error until I select a date in the the slicer.

It would be great if you can please help with me with this.

Again, thank you so much for all your help.

Vishesh Jain

[Zubair_Muhammad · Accepted solution]

Hi @mail2vjj

Try this MEASURE

Measure =
VAR mypreviousdate =
    CALCULATE (
        MAX ( TableName[Date] ),
        FILTER (
            ALLEXCEPT ( TableName, TableName[Name] ),
            TableName[Date] < SELECTEDVALUE ( TableName[Date] )
        )
    )
RETURN
    SUM ( TableName[Holding] )
        - CALCULATE (
            SUM ( TableName[Holding] ),
            FILTER (
                ALLEXCEPT ( TableName, TableName[Name] ),
                TableName[Date] = mypreviousdate
            )
        )

[Zubair_Muhammad]

@mail2vjj