How i can compare last week average to todays data in Power BI visual?

user_17681852 0
Viewed 55

I want to show an hourly score for today that refreshes every hour. I built it and it works, but now I want it to compare the hourly average for the last week and if it was higher it shows a red arrow up if lower it shows a green arrow down. I don't have a problem adding the arrows as it's very easy, but I've previously added a column to the query that shows if day = today and then used it as a filter inside the visual to show today's data, so when I try to compare the results the filter also affects the calculation I created:

Measure = calculate(average(rawdata[contacts]),rawdata[Week to Average = 1)

week to average is the column that tells if the week was the previous week is simply if(weekcolumn=weeknum(today())-1,1,0)

Do you know any way i can compare last week average to todays data? Also visual that i used is a matrix

dax powerbi powerquery
1 Answers

This is just an idea. So if it is enough to solve the case then ok. If it's not it, then, please, add more info about your data table - a screenshot or sample data. how you calculate your average and what do you mean by average? At least what kind of data you are dealing with is it a sum of some values per day, do you have a different number of values for each day? etc.

measure 1:

averContacts = AVERAGE(rawdata[contacts]) -- no CALCULATE()

measure 2:

avrToday = 
    CALCULATE(
         [averContacts]
         ,TreatAS({TODAY()}, yourTable[DatesColumn])
)

measure 3:

aveLastWeek =

VAR prevWeekEnd = TODAY() - WEEKDAY(Today(),2) -- 2 -> Mon-Sun week format
VAR prevWeekStart = prevWeekEnd - 7
VAR DatesLastWeek = CALENDAR(prevWeekStart ,prevWeekEnd)
RETURN
    CALCULATE(
             [averContacts]
             ,TreatAS(DatesLastWeek, yourTable[DatesColumn])
    )

measure for your visual

lastWeek_vs_Today = DIVIDE(avrToday ,aveLastWeek)
user_190423790
Related