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...
  • 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. 

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
    )
)

 

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

n8ball Thanks for the suggestion. I will check it out.

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

      Thanks again n8ball. So many resources, it's difficult to keep track. Especially for someone starting out.
      I would give you Kudos but I can't see an option for that. Has it been removed?