Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
AndyDDC
Solution Sage
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.

 

AndyDDC_0-1659551617265.png

 

1 ACCEPTED SOLUTION
tamerj1
Super User
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] ) 
)

View solution in original post

4 REPLIES 4
tamerj1
Super User
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] ) 
)

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.

Hi @AndyDDC 

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

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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