Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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 ) )
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 ) )
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 ) )
@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
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
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
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 ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |