count the difference between actual and target group by date

[Anonymous]

Hi all,

I have two tables. One with real data, and one with target for each day.

I'm having problems with creating a measure that groups the 'actual data' by each day and calculates how many days the sum over Actual for the day is greater then the target.

This is the tables that i have

i need to create  two measures so i have something like this

[Mariusz · Accepted solution]

Hi @Anonymous 

You can create below Measures.

Actual AMT = SUM( Actual[Amount] )

Target AMT = 
CALCULATE( 
    SUM( Target[Amount] ), 
    TREATAS( VALUES(Actual[Date]) , Target[Date] ) 
)  

Days Over Target = 
COUNTROWS(
    FILTER(
        VALUES( Actual[Date] ),
        [Actual AMT] >= [Target AMT]
    )
)

Days Under Target = 
COUNTROWS(
    FILTER(
        VALUES( Actual[Date] ),
        [Actual AMT] < [Target AMT]
    )
)

Regards,
Mariusz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Cmcmahan]

I did it by creating a calculated column on the target table like so: 

Actual Amount = CALCULATE(SUM(Actual[Amount]),FILTER(Actual,[Date]=EARLIER(Target[Date])))

Then you can create an easy pair of measures to check the values:

MeasureTargetHit = COUNTAX(Target,IF(Target[Actual Amount]>=Target[Amount],1,BLANK()))

MeasureTargetMissed = COUNTAX(Target,IF(Target[Actual Amount]<Target[Amount],1,BLANK()))

If you want to avoid creating the calculated column for whatever reason, you can replace the Target[Actual Amount] in the measures with the CALCULATE expression from the first DAX query.