Forum Discussion

ilcaa72's avatar
ilcaa72
Icon for Helper IV rankHelper IV
8 years ago
Solved

if Min date then 0

 

 

i am substracting the current value from previous months value with this formula (it seems like its working, even though the table doesnt display the diff but the card does)  BUT for the First date, it shows the entire value (see card below 97.89), i would rather just set it to Zero if the date is the minimal date then "0".

 I am having trouble structuring this part of the DAX... if current date = Min(date) then 0.

 

 

Diff = 
SUMX(MatchRate, MatchRate[Rate])   - 
CALCULATE(SUMX (MatchRate, MatchRate[Rate]), PREVIOUSMONTH(MatchRate[Date]))

 

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    Hello ilcaa72

     

    I made this, in my example works very well according to your requeriments, try it, and if doesn't work, I can work on another formula.

     

    DIFF =
    VAR Minimun_Date =
    CALCULATE ( MIN ( Date[Date] ), ALLSELECTED ( Date[Date] ) )
    VAR Date_to_Compare =
    MIN ( Date[Date] )
    RETURN
    IF (
    Date_to_Compare = Minimun_Date,
    0,
    SUMX ( MatchRate, MatchRate[Rate] )
    - CALCULATE (
    SUMX ( MatchRate, MatchRate[Rate] ),
    PREVIOUSMONTH ( MatchRate[Date] )
    )
    )

     

    Good Luck.

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hello ilcaa72

     

    I made this, in my example works very well according to your requeriments, try it, and if doesn't work, I can work on another formula.

     

    DIFF =
    VAR Minimun_Date =
    CALCULATE ( MIN ( Date[Date] ), ALLSELECTED ( Date[Date] ) )
    VAR Date_to_Compare =
    MIN ( Date[Date] )
    RETURN
    IF (
    Date_to_Compare = Minimun_Date,
    0,
    SUMX ( MatchRate, MatchRate[Rate] )
    - CALCULATE (
    SUMX ( MatchRate, MatchRate[Rate] ),
    PREVIOUSMONTH ( MatchRate[Date] )
    )
    )

     

    Good Luck.

    • ilcaa72's avatar
      ilcaa72
      Icon for Helper IV rankHelper IV
      8 years ago

      thanks Harrison. i implemented the solution, but the Column goes to 0 for all numbers and the card also to zero, no matter how I change the date filter... so i assume it is always returning TRUE for the variable comparison

       

      for the 2 variables, the Date table/column should be the same as the formula date table/columns in SUMX below it..., correct?

       

      VAR Minimun_Date = CALCULATE(MIN( MatchRate[Date]), ALLSELECTED ( MatchRate[Date] ))
      VAR Date_to_Compare = MIN( MatchRate[Date] )

       

      Diff = 
VAR Minimun_Date = CALCULATE(MIN(MatchRate[Date]), ALLSELECTED (MatchRate[Date])) 
VAR Date_to_Compare = MIN(MatchRate[Date])
RETURN
IF( Minimun_Date = Date_to_Compare,0,
SUMx(MatchRate, MatchRate[MatchedRate]) 
- CALCULATE(SUMx(MatchRate, MatchRate[MatchedRate]), 
			PREVIOUSMONTH(MatchRate[Date])))

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        ilcaa72 What Date is the one you use on the slicer?