Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
Any chance you can share the file?
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |