Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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]))
Solved! Go to 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.
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.
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])))
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()
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.
the same date as the table and in my original formula
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
85 | |
83 | |
66 | |
60 | |
58 |
User | Count |
---|---|
194 | |
111 | |
105 | |
79 | |
71 |