Forum Discussion
Relative Date using multiple dates
Try storing the bonus start date in a variable
Product target filtered sales = SUMX ( 'TblTargets(2)', VAR bonusStartDate = 'TblTargets(2)'[Product bonus start date] RETURN CALCULATE ( SUM ( 'Nav_Sales History MASTER'[Amount (LCY)] ), KEEPFILTERS ( 'Nav_Sales History MASTER'[Order date] >= bonusStartDate ) ) )
Gave that another go typing it out from scratch but no use again. Have tried it an alternative way...
So just for my sanity to make sure that I'm conveying myself properly. The Sales date that I need to filter on lives in TblTargets(2) (there are 2 dates, start date and product bonus start date, with the latter being the date that I want them to get bonus sales from).
Nav_Sales History MASTER holds the £amount and also holds the Order Date. So if the Product Bonus Start Date is 01/11/22, then any sales with an order date >= 01/11/22 should be output. So when I amend your suggestion to make sure the order date is include (as I think it's necessary), then when I try to include the Product Bonus Start Date column which sits in 'TblTargets (2), I cannot do that. The only options are either measures or the Nav_Sales History MASTER Table. I have tried to use RELATED like below...but no luck either
Try storing the bonus start date in a variable
Product target filtered sales =
SUMX (
'TblTargets(2)',
VAR bonusStartDate = 'TblTargets(2)'[Product bonus start date]
RETURN
CALCULATE (
SUM ( 'Nav_Sales History MASTER'[Amount (LCY)] ),
KEEPFILTERS ( 'Nav_Sales History MASTER'[Order date] >= bonusStartDate )
)
)