Measure using DATEADD(), split by day of week.

[robertvdleeuw]

With some help from this forum, I got the following measure set up: 

    Year behind Avg Revenue =

    CALCULATE(

        AVERAGE('Growth Metrics'[Revenue]),

        DATEADD('Growth Metrics'[Date], -1, YEAR))

I use it to compare the the sales of each month, split by year:

I'd like to create a similar visual, split not by month but by the day of the week. So that I could, for instance, compare avg sales on monday of 2018 to 2019. I have a datetable set up with all of the necessary information (yes, I know 'date' is misspelled. It's not my dataset):

The problem is that when I try to plot 'weekday' on the X-axis of the aforementioned visual, I get the following error.

I believe that this problems occurs because 'weekday' is not part of the date hierarchy of 'dat', and therefore not linked to the date in the way PBI wants it to be. I can think of a way to solve this issue using another table, but I'd prefer to solve this using in a more elegant way, like adding only one measure or perhaps just changing 'Avg Revenue Growth'. How would I go about fixing this? 

Thanks in advance!

[tamerj1]

Hi @robertvdleeuw 
You may also try

Year behind Avg Revenue =
CALCULATE (
    AVERAGE ( 'Growth Metrics'[Revenue] ),
    'Growth Metrics'[Year]
        = MAX ( 'Growth Metrics'[Year] ) - 1
)

[colacan]

@robertvdleeuw  Hi Rovertvdleeuw,

If you made date table and it is connected with your factable already, you can try this:

First make a avg revenue measure

Avg Revenue =  AVERAGE('Growth Metrics'[Revenue])

Then make a previous avg revenue measure

Last yearr Avg Revenue =  calculate([Avg Revenue],  DATEADD('yourdatetable'[Date], -1, YEAR))

Once you had these two measures, you can compare them by creating required measures. To create measure, simply put any filter in CALCULATE.
for example - "Monday"

Avg Monday Revenue =  CALCULATE([Avg Revenue], yourdatetable[weekday] = "Mon")
Lastyear Avg Monday Revenue =  CALCULATE([Last yearr Avg Revenue], yourdatetable[weekday] = "Mon")

Hope this helps you. thanks.