Forum Discussion
Show Changes between two given periods
Hi,
I'm working on a report that shows every months the lists of projects we have in our CRM.
I would like to show the changes in projects from a period to another but I have some issues.
What I have currently :
Let's say that in october we had 3 Projects as below (With Px a given project):
| October | amount |
| P1 | 12 |
| P2 | 5 |
| P3 | 5 |
| Total | 22 |
And in december we have that :
| December | |
| P2 | 6 |
| P3 | 4 |
| P4 | 10 |
| Total | 20 |
So Project 1 has been lost and Project 4 has been created.
I've create a simple formula that calculate the difference between October and December and when I display in a table with the poject in rows that's what I see:
| Changes | Amount |
| Total | -2 |
| P2 | 1 |
| P3 | -1 |
So the Total is correct, I went from 22 to 20 so it reduce from 2. But in my table I see only the two projects october and december have in commun. And what I would like to see is that :
| Changes | amount |
| Total | -2 |
| P1 | -12 |
| P2 | 1 |
| P3 | -1 |
| P4 | 10 |
My formula is simply:
Net Changes = CALCULATE( Net sales, Month = December) - CALCULATE(Net sales, Month = October).
Thank you for reading my text, if you have an idea on how to solve my issue it would be great.
Thanks,
AROTH
Hi Aroth,
Measure = CALCULATE([net]),FILTER(ALL(MONTHS),MONTHS[index] = MAX(MONTHS[index]))-CALCULATE([net]),FILTER(ALL(MONTHS), MONTHS[index] = MIN(MONTHS[index] )Based on above measure, I could get the desired output.
What we need is add a MonthNo column to original table (suppose it's 'Month-Project'), and insert an extra table (in my test, it's named as 'Month') listing unique month values.
Create measure like:
Diff = CALCULATE ( ( [net] ), FILTER ( 'Month-Project', 'Month-Project'[MonthNo] = MAX ( 'Month'[MonthNo] ) ) ) - CALCULATE ( ( [net] ), FILTER ( 'Month-Project', 'Month-Project'[MonthNo] = MIN ( 'Month'[MonthNo] ) ) )Add field [Month] from 'Month' table. Add [Project] and [Diff] from 'Month-Project' table.
Best regards,
Yuliana Gu
8 Replies
I think that if you use EXCEPT along with a UNION and a NATURALINNERJOIN that you could essentially return a table with all of your exceptions. So the NATURALINNERJOIN would find the matching rows between the tables. UNION would UNION your tables together and so if you take the EXCEPT of the UNION and NATURALINNERJOIN you would have the differences.
Greg_Deckler thank you for answer, I'm not sure how I can use that as everything is on the same data base (table):
Month Project net October P1 12 October P2 5 October P3 5 December P2 6 December P3 4 December P4 10 That's why I use CALCULATE to filter on one month and substract by another CALCULATE filtered on an other month.
And my "net" field is a measure...Wow, I way over complicated that. Just create a measure:
Measure = CALCULATE(SUM(Projects[net]),Projects[Month]="December")-CALCULATE(SUM(Projects[net]),Projects[Month]="October")
Then put your Project column and this measure into a Table visualization.
