Forum Discussion

music43's avatar
music43
Icon for Advocate II rankAdvocate II
4 years ago
Solved

Stop measure results after a specific date

Hi

 

I am playing with the PBI Desktop (finance) dummy data to try and learn DAX. I am trying to stop Sales PY measure from returning a result after the last Sale date. I am pretty certain this DAX should work, which makes wonder if the problem is in the data/model.

 

Sales table

 

Budget table (added by me - not in original dataset)

 

Just in case you need to see, my [Sales Amt] measure:

Sales Amt =
SUMX(
    Sales,
    Sales[Units Sold] * Sales[Sale Price]
)


Can someone please help?

  • tamerj1's avatar
    tamerj1
    4 years ago

    music43 

    Actually with my suggestion you should use MAX ( Date[Date] ) as condition not SELECTEDVALUE. 

    What is happening hear is that SELECTEDVALUE returns blank everywhere while MAX ( Sales[Date] ) returns blank when there is no sale which did the job (blank = blank). But it won't work if you are slicing by Date instead of MonthYear. 

8 Replies

  • n8ball's avatar
    n8ball
    Icon for Advocate I rankAdvocate I
    4 years ago

    This is the way I do it. Add a DatesInThePast True / False column to your Date Table. 

    Sales Amt PY = 
CALCULATE (
    [Sales Amt],
    CALCULATETABLE (
        SAMEPERIODLASTYEAR ( 'Date'[Date] ),
        'Date'[DatesInThePast] = TRUE
    )
)

     

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    music43 

    VAR LastSalesDate =
    CALCULATE (

        MAX ( Sales[Date] ), REMOVEFILTERS ( 'Date' ) 

    )

    • music43's avatar
      music43
      Icon for Advocate II rankAdvocate II
      4 years ago

      Hi tamerj1 

      I tried what you suggested, but get the same result

      Interestingly, the MAX( Sales[Date] ) seems to be returning what I was expecting.

       

       

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        4 years ago

        music43 

        Actually with my suggestion you should use MAX ( Date[Date] ) as condition not SELECTEDVALUE. 

        What is happening hear is that SELECTEDVALUE returns blank everywhere while MAX ( Sales[Date] ) returns blank when there is no sale which did the job (blank = blank). But it won't work if you are slicing by Date instead of MonthYear.