Help needed with calculating value between different date periods

[Anonymous]

Hello everyone,

I am trying to create a dynamic line graph that will show the values over time, but I would like to colour format it based on if the value is negative or positive.

So created 2 measures, one that returns a line if the value1 is greater than value2 else blank, and then another measure that should display a line if the value1 is less than value2 negative, else blank.

The reason I am doing this is so I can have a different colour for each line, and quickly see across many different visuals the ones that have a negative trend.

My problem starts when I try to get the values for specific date periods.

I created 2 variables,
1. one that gets the value for the past 7 days from max date
2. one that gets the value for the previous 7 days from the past 7 days, if that makes sense.

Here are the measures

Positive trend = 

//GLOBAL
VAR selectedPeriod = SELECTEDVALUE(xCompare[ID])
VAR sessions = SUM(Traffic[Sessions])

//CALCULATIONS
VAR past7days = CALCULATE(SUM(Traffic[Sessions]), FILTER(Traffic, Traffic[Date] > MAX(Traffic[Date]) - 7 && Traffic[Date] <= MAX(Traffic[Date])))
VAR previous7days = CALCULATE(SUM(Traffic[Sessions]), FILTER(Traffic, Traffic[Date] > (MAX(Traffic[Date]) - 7) - 7 && Traffic[Date] <= MAX(Traffic[Date]) - 7))
VAR past30days = CALCULATE(SUM(Traffic[Sessions]), FILTER(Traffic, Traffic[Date] > MAX(Traffic[Date]) - 30 && Traffic[Date] <= MAX(Traffic[Date])))
VAR previous30days = CALCULATE(SUM(Traffic[Sessions]), FILTER(Traffic, Traffic[Date] > (MAX(Traffic[Date]) - 30) - 30 && Traffic[Date] <= MAX(Traffic[Date]) - 30))
VAR past60days = CALCULATE(SUM(Traffic[Sessions]), FILTER(Traffic, Traffic[Date] > MAX(Traffic[Date]) - 60 && Traffic[Date] <= MAX(Traffic[Date])))
VAR previous60days = CALCULATE(SUM(Traffic[Sessions]), FILTER(Traffic, Traffic[Date] > (MAX(Traffic[Date]) - 60) - 60 && Traffic[Date] <= MAX(Traffic[Date]) - 60))
VAR past90days = CALCULATE(SUM(Traffic[Sessions]), FILTER(Traffic, Traffic[Date] > MAX(Traffic[Date]) - 90 && Traffic[Date] <= MAX(Traffic[Date])))
VAR previous90days = CALCULATE(SUM(Traffic[Sessions]), FILTER(Traffic, Traffic[Date] > (MAX(Traffic[Date]) - 90) - 90 && Traffic[Date] <= MAX(Traffic[Date]) - 90))

//CONDITIONS
VAR compare7 =   IF(past7days > previous7days, sessions, BLANK())
VAR compare30 =   IF(past30days > previous30days, sessions, BLANK())
VAR compare60 =   IF(past60days > previous60days, sessions, BLANK())
VAR compare90 =   IF(past90days > previous90days, sessions, BLANK())

//DISPLAY
VAR display = SWITCH( TRUE(),
                selectedPeriod = 1, compare7,
                selectedPeriod = 2, compare30,
                selectedPeriod = 3, compare60,
                selectedPeriod = 4, compare90,
                compare30
)

VAR ReturnResult = display

RETURN
ReturnResult

Negative trend = 

//GLOBAL
VAR selectedPeriod = SELECTEDVALUE(xCompare[ID])
VAR sessions = SUM(Traffic[Sessions])

//CALCULATIONS
VAR past7days = CALCULATE(SUM(Traffic[Sessions]), FILTER(Traffic, Traffic[Date] > MAX(Traffic[Date]) - 7 && Traffic[Date] <= MAX(Traffic[Date])))
VAR previous7days = CALCULATE(SUM(Traffic[Sessions]), FILTER(Traffic, Traffic[Date] > (MAX(Traffic[Date]) - 7) - 7 && Traffic[Date] <= MAX(Traffic[Date]) - 7))
VAR past30days = CALCULATE(SUM(Traffic[Sessions]), FILTER(Traffic, Traffic[Date] > MAX(Traffic[Date]) - 30 && Traffic[Date] <= MAX(Traffic[Date])))
VAR previous30days = CALCULATE(SUM(Traffic[Sessions]), FILTER(Traffic, Traffic[Date] > (MAX(Traffic[Date]) - 30) - 30 && Traffic[Date] <= MAX(Traffic[Date]) - 30))
VAR past60days = CALCULATE(SUM(Traffic[Sessions]), FILTER(Traffic, Traffic[Date] > MAX(Traffic[Date]) - 60 && Traffic[Date] <= LASTDATE(Traffic[Date])))
VAR previous60days = CALCULATE(SUM(Traffic[Sessions]), FILTER(Traffic, Traffic[Date] > (MAX(Traffic[Date]) - 60) - 60 && Traffic[Date] <= MAX(Traffic[Date]) - 60))
VAR past90days = CALCULATE(SUM(Traffic[Sessions]), FILTER(Traffic, Traffic[Date] > MAX(Traffic[Date]) - 90 && Traffic[Date] <= MAX(Traffic[Date])))
VAR previous90days = CALCULATE(SUM(Traffic[Sessions]), FILTER(Traffic, Traffic[Date] > (MAX(Traffic[Date]) - 90) - 90 && Traffic[Date] <= MAX(Traffic[Date]) - 90))

//CONDITIONS
VAR compare7 =   IF(past7days < previous7days, sessions, BLANK())
VAR compare30 =   IF(past30days < previous30days, sessions, BLANK())
VAR compare60 =   IF(past60days < previous60days, sessions, BLANK())
VAR compare90 =   IF(past90days < previous90days, sessions, BLANK())

//DISPLAY
VAR display = SWITCH( TRUE(),
                selectedPeriod = 1, compare7,
                selectedPeriod = 2, compare30,
                selectedPeriod = 3, compare60,
                selectedPeriod = 4, compare90,
                compare30
)

VAR ReturnResult = display

RETURN
ReturnResult

What happens is that the graph always displays the positive trend line, even if the value 1 is less than value 2.

I want to do this across different date periods, 7, 30, 60, 90 days, as you can see from the code above, based on the period that has been selected.

The graph will always be the same, the line will always look the same, just the colour will change.

I have tried many different approaches to calculate the 2 values, but it always returns a positive trend.

I hope the above makes sense. Any help will be much appreciated.

Regards
George 

[Greg_Deckler]

Any chance you can share the file?

[Anonymous]

Hey @Greg_Deckler 

Here you go https://we.tl/t-MvUrbrZof0

[Anonymous · Accepted solution]

I fixed the issue using the measure below

Positive trend = 

//GLOBAL
VAR selectedPeriod = SELECTEDVALUE(xCompare[ID])
VAR sessions = SUM(Traffic[Sessions])

//CALCULATIONS
VAR pastDays = IF( selectedPeriod = 1, CALCULATE(SUM(Traffic[Sessions]),  FILTER(ALL(xDates), xDates[Previous 7 Days] = "Yes")), 
                    IF( selectedPeriod = 2, CALCULATE(SUM(Traffic[Sessions]), FILTER(ALL(xDates), xDates[Previous 30 Days] = "Yes")),
                        IF( selectedPeriod = 3, CALCULATE(SUM(Traffic[Sessions]), FILTER(ALL(xDates), xDates[Previous 60 Days] = "Yes")),
                            IF( selectedPeriod = 4, CALCULATE(SUM(Traffic[Sessions]), FILTER(ALL(xDates), xDates[Previous 90 Days] = "Yes")))
                        )
                    )
)
VAR previousDays = IF( selectedPeriod = 1, CALCULATE(SUM(Traffic[Sessions]), FILTER(ALL(xDates), xDates[Past 7 Days] = "Yes")), 
                    IF( selectedPeriod = 2, CALCULATE(SUM(Traffic[Sessions]), FILTER(ALL(xDates), xDates[Past 30 Days] = "Yes")),
                        IF( selectedPeriod = 3, CALCULATE(SUM(Traffic[Sessions]), FILTER(ALL(xDates), xDates[Past 60 Days] = "Yes")),
                            IF( selectedPeriod = 4, CALCULATE(SUM(Traffic[Sessions]), FILTER(ALL(xDates), xDates[Past 90 Days] = "Yes")))
                        )
                    )
)

VAR conditions = SWITCH(
                    TRUE(),
                    pastDays > previousDays, sessions,
                    pastDays < previousDays, BLANK(),
                    BLANK()
)


VAR ReturnResult = conditions

RETURN
ReturnResult