Forum Discussion

jyeager's avatar
jyeager
Helper I
7 years ago
Solved

Need Help with Measure - Current Versus Prior Month IF Statement

Hi Everyone! I am having a bit of trouble with a Measure I created to help return a calculated value in a certain date range. The probelm isnt with the calculation itself, it is regarding a time filter I want to place on the calculcation. Basically, I want to have the value/result of the calcuation be "0" if the month has passed and have the calcuation perform as written if we are in the current month. Here is the DAX I have to calculate the value I need for when we are in the current month. Any idea on how to apply and IF statement (or other function) to return a "0" for prior months? 

 

Thanks in advance for your help!!

 

JY

 

Measure:

Points to Go = CALCULATE(SUM(LEX_Points_DB_Forecast[Points plan]),DATESBETWEEN(LEX_Points_DB_Forecast[Date],TODAY(),EOMONTH(TODAY(),0)))

 

I want o be able to visualize that the "Points to Go" for prior months is "0". See screenshots below. Right now, my card shows "(blank)" and I'd like it to say "0". (see data in red circle).

 

 

For the current month (October), it correctly shows the calculated value. (see data in red circle).

 

Need Help
Tips and Tricks
  • Greg_Deckler's avatar
    Greg_Deckler
    7 years ago

    Perhaps just put that calculation in a VAR statement and then RETURN an IF statemant that is essentially like this:

     

    Points to Go = 
VAR __myCalc = CALCULATE(SUM(LEX_Points_DB_Forecast[Points plan]),DATESBETWEEN(LEX_Points_DB_Forecast[Date],TODAY(),EOMONTH(TODAY(),0)))
RETURN
IF(ISBLANK(__myCalc),0,__myCalc)

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    7 years ago

    Perhaps just put that calculation in a VAR statement and then RETURN an IF statemant that is essentially like this:

     

    Points to Go = 
VAR __myCalc = CALCULATE(SUM(LEX_Points_DB_Forecast[Points plan]),DATESBETWEEN(LEX_Points_DB_Forecast[Date],TODAY(),EOMONTH(TODAY(),0)))
RETURN
IF(ISBLANK(__myCalc),0,__myCalc)