Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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
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.
Can you kindly elaborate more with the simple data set provided by requester. Also can you help how to solve it with drill through
@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.
That's what I did, the only thigs Iv'e added is :
Measure = CALCULATE([net]),FILTER(ALL(MONTHS),MONTHS[index] = MAX(MONTHS[index]))
-CALCULATE([net]),FILTER(ALL(MONTHS), MONTHS[index] = MIN(MONTHS[index] )
So it enable to compare two selected periods ...
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
Hi thanks for the support, I have the same scenario, when I use the fax in drill through option. It doest show correct caluculation. Can you kindly help what could be the possible issue and why Im not able get the values populated correctly when I used the fax as a column in tablix which is created for drill trhrrough. I have proper relationships with in table. Happy to discuss
Thanks !!
I finaly understood my issue. The relation between the two tables had the cross filter selection in both ways!!
I changed it to "single" and now it works 🙂
User | Count |
---|---|
130 | |
108 | |
101 | |
67 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |