Forum Discussion

Tob_P's avatar
Tob_P
Helper V
4 years ago
Solved

Relative Date using multiple dates

Hi there - just trying to see if this is possible or if anyone has done something similar.   I have a table that shows product sales and you can slice on those salespeople within in. However I want...
  • johnt75's avatar
    johnt75
    4 years ago

    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 )
        )
)
Tob_P's avatar
Tob_P
Helper V
4 years ago

johnt75 

 

Sorry for delayed response. Have given this a go but I'm not quite there...

 

 

So my Saleserson Start Date in in TBlTargets (2) as is the saleperson name, code etc, the Sales Amount is in Nav_Sales, I have my Date table fine but when I look to bring in the Start Date from TblTargets (2), it will only allow me to select either the Date table or a Measure from another table.

 

For info, I had to substitute Calculate [sales amount] from your suggestion above, as the Sales Amount isn't a measure.

 

Any guidance appreciated!

johnt75's avatar
johnt75
Super User
4 years ago

I think that might just be Intellisense being rubbish. As you're iterating over 'TblTargets(2)' you have a row context so you can access any column from that table. Try just typing in the fully qualified name, i.e. 'TblTargets(2)'[Start date]