Forum Discussion
Substract same figures from each category
Hello,
If possible maybe someone can support with a solution, I will try to describe my situation:
I have a growing database, each month a new version is uploaded:
| Customer | Planned Vol | Actual Volumes | Source |
| Daimler | 100 | 50 | Jan-20 |
| VW | 50 | 20 | Jan-20 |
| Daimler | 90 | 40 | Feb-20 |
| VW | 60 | 20 | Feb-20 |
| Daimler | 100 | 40 | Mar-20 |
| VW | 60 | 30 | Mar-20 |
I would like to calculate for each of my sources ( file from Jan , file from Feb..) the difference between the planned volumes and the latest actual volumes. I make a measure to get the latest actual volumes (not sure if this is the way to go)
Latest actuals = var max_date = LASTDATE('Overview'[Source])
return CALCULATE(SUM('Overview'[Actual volumes]), FILTER('Overview','Overview'[Source] = max_date))
In the example the Daimler 40 / VW 30
Now I want to take the latest volumes and substract them from each monthl version from my database:
| Source | Planned Vol | Lates Actual Volumes | Difference | |
| Jan | Daimler | 100 | 40 | 60 |
| VW | 50 | 30 | 20 | |
| Feb | Daimler | 90 | 40 | 50 |
| VW | 60 | 30 | 30 | |
| Mar | Daimler | 100 | 40 | 60 |
| VW | 60 | 30 | 30 |
The difference should be the result.
1 I don'y have any ideea how to achieve that
2 If I try a quick measure to substract the result is only substracting the figures from the March version.
Open to any ideas that might help.
Thank you
Anonymous ,
Create two measures using dax as below:
Latest actuals = VAR max_date = CALCULATE(MAX( 'Overview'[Source] ), ALL(Overview)) RETURN CALCULATE ( MAX( 'Overview'[Actual volumes] ), FILTER ( ALLEXCEPT('Overview', Overview[Customer]), 'Overview'[Source] = max_date ) ) Result = MAX(Overview[Planned Vol ]) - [Latest actuals]The result is like below:
You can also refer to the attached pbix file.
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
It looks like you've already solved the hard part. Why doesn't this work with the two measures you already have?
Difference = [Planned Volume] - [Latest Actual Vol]
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
Anonymous ,
Create two measures using dax as below:
Latest actuals = VAR max_date = CALCULATE(MAX( 'Overview'[Source] ), ALL(Overview)) RETURN CALCULATE ( MAX( 'Overview'[Actual volumes] ), FILTER ( ALLEXCEPT('Overview', Overview[Customer]), 'Overview'[Source] = max_date ) ) Result = MAX(Overview[Planned Vol ]) - [Latest actuals]The result is like below:
You can also refer to the attached pbix file.
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
