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

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

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.