Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Help needed with calculating value between different date periods

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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
Greg_Deckler's avatar
Greg_Deckler
Community Champion
7 years ago

Any chance you can share the file?

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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