Calculating Targets vs Actuals using From and To Dates

[AndyDDC]

Hi,

I have a scenario where there are Sales Targets being stored in a table with EffectiveFrom and To dates which indicate the period of time the target was active for.  EG a sales person initially has a yearly target of 500, but they get promoted so the old target is closed off and a new target is created.  I need to be able to generate a % of the Target reached for each period of the target.

The image shows the targets table and the sales table.  I believe I need to calculate the sales amount using the SalesDate between the target effectivefrom and to columns from the target table, but I'm unsure how to do this in DAX.  Any advice would be greatly appreciated.

[tamerj1 · Accepted solution]

Hi @AndyDDC 
I would suggest to use CALENDAR function to create a temporary dates table from start of period to end of period then use TREATAS to give this table the lineage of date table of your data model. Something like:

VAR DatesPeriod = CALENDAR (  'Sales Targets '[TargetEffectiveFrom],  'Sales Targets '[TargetEffectiveTo] )
RETURN
CALCULATE (
[Sales Amount],
TREATAS ( DatesPeriod, 'Date'[Date] ) 
)

[AndyDDC]

Thank you for the suggestion and example code.  I'm trying to implement but when I'm creating the measure, the Sales Targets table (and the date columns) don't appear to be visible when creating the measure.  If I copy in the names then I get red error lines, and they don't appear in intellisense either.

[tamerj1]

Hi @AndyDDC 

Can you please share some screenshots for better understanding of the problem. Seems you don't have a date table?

[AndyDDC]

I ws passing in the wrong table for the dates, your solution is now working. Thanks