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.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors