Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I want to create measure/column that will check if values are going up or down.
I have three columns (ID,date,value)
Matrix example.
Here is the what I want
If decrease in value is up to 150, I want result to be = 1
If decrease in value is over 150, I want result to be = 2
If increase in value is up to 100, I want result to be = 3
If increase in value is over 100, I want result to be = 4
I am not sure how I am suppose to calculate values between previous dates, every comment is helpful.
Thanks a lot!
Solved! Go to Solution.
Hi,
Please check the below picture and the attached pbix file.
Value measure: =
SUM( Data[Value] )
Flag measure: =
VAR currentvalue = [Value measure:]
VAR previousvalue =
CALCULATE ( [Value measure:], 'Calendar'[Date] = MAX ( 'Calendar'[Date] ) - 1 )
VAR comparisonvalue = currentvalue - previousvalue
RETURN
IF (
currentvalue <> BLANK ()
&& previousvalue <> BLANK (),
SWITCH (
TRUE (),
comparisonvalue < -150, 2,
comparisonvalue >= -150
&& comparisonvalue < 0, 1,
comparisonvalue >= 0
&& comparisonvalue <= 100, 3,
comparisonvalue > 100, 4
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Thanks a lot for all your comments!
All three solutions are good, but I prefered the one I accepted.
Best nemp
Hi @Anonymous ,
Try this:-
Column =
var PreviousDate = MAXX(FILTER(ALL('Table (2)'),EARLIER([Date]) > [Date] ),[Date])
var previousValue = CALCULATE(MAX([value]),FILTER('Table (2)',[Date] = PreviousDate))
var change = if(previousValue <> blank(),previousValue - [value],[value])
Return SWITCH(TRUE(),
previousValue <> blank() && change <= -150,1,
previousValue <> blank() && change > -150,2,
previousValue <> blank() && change <100,3,
previousValue <> blank() && change >100,4)
Output:-
Thanks,
Samarth
Best Regards,
Samarth
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin
Hi,
Please check the below picture and the attached pbix file.
Value measure: =
SUM( Data[Value] )
Flag measure: =
VAR currentvalue = [Value measure:]
VAR previousvalue =
CALCULATE ( [Value measure:], 'Calendar'[Date] = MAX ( 'Calendar'[Date] ) - 1 )
VAR comparisonvalue = currentvalue - previousvalue
RETURN
IF (
currentvalue <> BLANK ()
&& previousvalue <> BLANK (),
SWITCH (
TRUE (),
comparisonvalue < -150, 2,
comparisonvalue >= -150
&& comparisonvalue < 0, 1,
comparisonvalue >= 0
&& comparisonvalue <= 100, 3,
comparisonvalue > 100, 4
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
@Anonymous . With help from date table joined with date of your table have measures like
This Day = CALCULATE(sum('Table'[value]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])))
Last Day = CALCULATE(sum('Table'[value]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])-1))
diff =[This Day] - [Last Day]
Then create a measure like
Switch( True(),
[diff] <-150 ,2 ,
[diff] <0 , 1 ,
[diff] <100 , 3 ,
4)
Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
User | Count |
---|---|
87 | |
72 | |
68 | |
64 | |
54 |
User | Count |
---|---|
98 | |
89 | |
74 | |
67 | |
62 |