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 )
        )
)
johnt75's avatar
johnt75
Super User
4 years ago

If you have a column on your sales person table which indicates the date from when they should receive credit for the sales, then you could create a measure like 

Filtered sales =
SUMX (
    'Sales person',
    CALCULATE (
        [Sales amount],
        KEEPFILTERS ( 'Date'[Date] >= 'Sales person'[Start date] )
    )
)