cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Solution Sage

## Calculating Targets vs Actuals using From and To Dates

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.

1 ACCEPTED SOLUTION
Super User

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] )
)

4 REPLIES 4
Super User

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] )
)

Solution Sage

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.

Super User

Hi @AndyDDC

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

Solution Sage

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

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors