How to create measure that will check if there is decrease or increase in value based on date

[Anonymous]

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!

[Anonymous]

Thanks a lot for all your comments!

All three solutions are good, but I prefered the one I accepted.

Best nemp 

[Samarth_18]

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

[Jihwan_Kim · Accepted 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
        )
    )

[amitchandak]

@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.