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]

    • Tob_P's avatar
      Tob_P
      Helper V
      4 years ago

      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

      • johnt75's avatar
        johnt75
        Super User
        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 )
        )
)