if Min date then 0

[ilcaa72]

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

[Anonymous · Accepted solution]

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]

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]

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

[ilcaa72]

i changed the ALLSELECTED to ALL and it is working correctly (changed your forumula so i can mark as correct).. so thank you....

BUT how can i get the difference in my table, the end results is to place it in a line graph.  i know have all zeros.  (screenshot)  could my original formula be wrong? should I not use the SUMX()

[Anonymous]

Hi @ilcaa72

I should work with the ALLSELECTED option, but in your case there's something important you are using the function "PREVIOUSMONTH" this is a time-intelligence function which works perfectly with a calendar table, so I encourage you to create a calendar table, maybe with that the the informations displayed will be the one you expected.

as you can see in my example works, but I'm using a normal SUM, I'm no using any time-intelligence function.

[ilcaa72]

the same date as the table and in my original formula