61/5000 Average sales for the last 3 days excluding Sundays

[arturocastillo]

Hello,

I have a problem with a calculated measure for the average sales for the last 3 days BUT excluding Sundays and considering the previous days for that. I have the next formula:

AVG_SALES = (DIVIDE(CALCULATE(SUM(sales);DATESINPERIOD(date;LASTDATE(date);-3;DAY));3))

And that give the next result:

But my expect result is:

For days near to Sunday, the result is different. For example, for Sunday I average 1150+1200+800 excluding Sunday. For Monday, I average 1500+800+1200 excluding Sunday and Finally, in Tuesday I average 1230+1500+800.

I await your comments to help me with this measure. Thank you very much

Arturo Castillo

[v-ljerr-msft · Accepted solution]

Hi @arturocastillo,

Based on my test, the formula below should work in your scenario. 

AVG_SALES = 
VAR currentDate =
    MAX ( Table1[Date] )
VAR currentWeekDay =
    WEEKDAY ( currentDate, 1 )
RETURN
    IF (
        currentWeekDay > 3,
        DIVIDE (
            CALCULATE (
                SUM ( Table1[Sales] ),
                FILTER (
                    ALL ( Table1 ),
                    currentDate - Table1[Date]
                        < 3
                        && currentDate - Table1[Date]
                        >= 0
                )
            ),
            3
        ),
        DIVIDE (
            CALCULATE (
                SUM ( Table1[Sales] ),
                FILTER (
                    ALL ( Table1 ),
                    currentDate - Table1[Date]
                        < 4
                        && currentDate - Table1[Date]
                        >= 0
                        && WEEKDAY ( Table1[Date], 1 ) <> 1
                )
            ),
            3
        )
    )

Regards