Substract same figures from each category

[Anonymous]

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

[v-yuta-msft · Accepted solution]

@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.

[mahoneypat]

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]

Hello

I tried and it's just subtracting the figures from the March version.

To my extent I identify the actual figures for Daimler 40 / VW 30 delays, plese find example with my current status

I want to subtract the March values from each source category.

[Anonymous]

Hello

I tried and it's just subtracting the figures from the March version.

To my extent I identify the actual figures for Daimler 40 / VW 30 delays, plese find example with my current status

I want to subtract the March values from each source category.

[Anonymous]

Hello,

I tried that and  it's only substracting the figures from the March version.

In my measure it identifies the lates actual figures Daimler 40/ VW 30 , plese find example with my current status

I want to subtract the March values from each source category.