Rolling Average sensitive to weekday

[738o51]

Hi everyone,

I am working on building a visual that should show an average daily census (ADC) for a period determined by a date slicer, by each day of week. I also want to add a line that shows a 12 month rolling average from which to compare the ADC for the selected time period.

I've tried two formulas and neither have accomlished what I want.

Formula A: 

The good: this formula is responsive to the weekday axis

The bad: this formula doesn't work when my selected time period is less than 12 months. Then, it defaults to equal the average for the time period selected.

Rolling Average 0600 12 ADC = 
Divide(
    CALCULATE(
        sum(Datebedfacil[0600]),
        ALL(Datebedfacil[0600]),
        DATESINPERIOD(
            Datebedfacil[Date],
            LASTDATE(Datebedfacil[Date]),
            -12,MONTH
        )
    )
    ,
    CALCULATE(
        DISTINCTCOUNT(Datebedfacil[Date]),
        ALL(Datebedfacil[Date]),
        DATESINPERIOD(
            Datebedfacil[Date],
            LASTDATE(Datebedfacil[Date]),
            -12,MONTH
        )
    )
)

Formula B:

The good: this works to show a 12 month average regarless of the time set by the slicer

The bad: this isn't responsive to the weekday axis.

Rolling Average 0600 12 ADC = 
Divide(
    CALCULATE(
        sum(Datebedfacil[0600]),
        ALL(Datebedfacil),
        DATESINPERIOD(
            Datebedfacil[Date],
            LASTDATE(Datebedfacil[Date]),
            -12,MONTH
        )
    )
    ,
    CALCULATE(
        DISTINCTCOUNT(Datebedfacil[Date]),
        ALL(Datebedfacil),
        DATESINPERIOD(
            Datebedfacil[Date],
            LASTDATE(Datebedfacil[Date]),
            -12,MONTH
        )
    )
)

The weekdays come from my date table. There is an active relationship between the Date[Date] column and the Datebedfacil[Date] column. 

Can anyone help? Unforunately I cannot share a .pbix due to private health information. Thanks in advance!

[sturlaws]

Hi @738o51,

perhaps you could create a relevant mockup report to share?

Not sure if I understand your requirements correctly, but assuming you have a calendar/date-dimension you can try the following code:

Measure =
VAR _maxSlicerDate =
    ( MAX ( dim_date[Date] ) )
VAR _dayOfWeek =
    MAX ( dim_date[WeekdayNum] )
RETURN
    CALCULATE (
        AVERAGE ( 'Table'[MockupValue] );
        FILTER (
            ALL ( dim_date );
            dim_date[WeekdayNum] = _dayOfWeek
                && dim_date[Date] >= _maxSlicerDate - 365
                && dim_date[Date] <= _maxSlicerDate
        )
    )

I have created a simple mockup report to demonstrate

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

[738o51]

Thanks for your help! Unforunately this didn't work.

Maybe this will help explain the problem:

ADC is my variable shown in columns. It equals the average daily census and is broken down by day of week.

Let's assume my date slicer is set to show 6 months.

If I use formula A, my chart looks like this:

If I use formula B, my chart looks like this:

Thanks again!

[738o51]

Sorry it looks like my second photo didn't load. Here is Formula B^