Calculate average daily margin for the last 3 months without using a slicer

[Kevin_Harper]

Hello,

I am trying to create a column in a visual that shows the average margin per specific day (i.e. Monday, Tuesday, etc.) for the last 3 months.

I am calculating the average per day correctly.  Here is my measure:

Average Margin per Day = AVERAGEX(VALUES('Calendar'[Date]), MarginData[Total Margin])

I have a date dimension table called Calendar with the range from one of the fact tables which is approximately

1 year worth of dates.

I have a slicer with the months listed and as I select the months that I want from the slicer, the average is calculating

and showing correctly in the visual.

The problem is that I want the measure to just look at the previous 3 months without considering the slicer since I am

using a visual that I have disconnected from the other slicers in the report.

I have tried several different date options that I have read online and just tried myself but I can't seem to come up with

the correct values.

Basically, I want to put this measure in a visual and get something like this:

I know it looks like my 4 year old spelled "Correct" but that was actual me with my awesome mouse skills.

The average in the list is correct but it requires me to select the 3 months in the slicer to the right of it. I would just

like it to show those values always without requiring the slicer.

Daily Add File

Above is a link to my desktop file so you can see what I am working with.

I appreciate the help.

[MFelix · Accepted solution]

Hi @Kevin_Harper ,

In order to ignore the slicer context you need to add an ALL or ALLSELECTED to your measure in your case when you are considering the WeekdayTable you are not ignoring that context so the result will not be over the last 3 months.

Redo your measure to:

Avg 3 Month REDONE =
VAR ThreeMonthDate =
    CALCULATE (
        DATEADD ( LASTDATE ( 'Calendar'[Date] ); -3; MONTH );
        ALL ( 'Calendar' )
    )
RETURN
    CALCULATE (
        [Average Margin per Day];
        FILTER ( 'Calendar'; 'Calendar'[Date] >= ThreeMonthDate )
    )

Should give expected result, be aware that I notice some small difference but believe is related with the calculation gets the information for the last date since you are getting different values for each line based on week day.

Regards,

MFelix

[Kevin_Harper]

You're awesome, that worked perfectly.   Pulling the numbers and doing the averages manually for the time period give them same results as your measure.

Thank you!

[MFelix]

Hi @Kevin_Harper ,

Just be carefull when you want to use the ALL or ALLSELECTED because depending on the placement on the measure the results can give you the same result in all rows of the visuals or can give you strange numbers, however this is the formulas to use to ignore all filter contexts on a measure or a calculation.

Regards,

MFelix

[Kevin_Harper]

Yes, I definitely need to study those functions to understand them better.  That seems to have made the biggest difference I was missing.

Thanks again!