How to Calculate Prior Day Total Daily Sales?

[Anonymous]

Hi, 

I am trying to calculate the Prior Day Total Sales. Tuesday would show Monday Total Sales, Saturday would show Friday Total Sales. Except Sunday would show Friday and Saturday Sales and Monday would show Friday, Saturday and Sunday Sales. I wrote the dax below but it isnt calculating correctly and I have tried using a Billing Date filter but it is incorrect.

PriorDaySales =

VAR CurrentDate = MAX('Billing Date'[Billing Date])

VAR CurrentYear = YEAR(CurrentDate)

VAR DayOfWeek = WEEKDAY(CurrentDate, 2)  

VAR SalesPriorDays =

    SWITCH(

        TRUE(),

        DayOfWeek = 6,  

            CALCULATE(

                SUM('Billed Orders'[BilledNetProductSales]),

                FILTER('Billing Date', 'Billing Date'[Billing Date] = CurrentDate - 1)  

            ) +

            CALCULATE(

                SUM('Billed Orders'[BilledNetProductSales]),

                FILTER('Billing Date', 'Billing Date'[Billing Date] = CurrentDate)  

            ),

        DayOfWeek = 7,  

            CALCULATE(

                SUM('Billed Orders'[BilledNetProductSales]),

                FILTER('Billing Date', 'Billing Date'[Billing Date] = CurrentDate - 1)  

            ) +

            CALCULATE(

                SUM('Billed Orders'[BilledNetProductSales]),

                FILTER('Billing Date', 'Billing Date'[Billing Date] = CurrentDate - 2)  

            ) +

            CALCULATE(

                SUM('Billed Orders'[BilledNetProductSales]),

                FILTER('Billing Date', 'Billing Date'[Billing Date] = CurrentDate)  

            ),

        CALCULATE(

            SUM('Billed Orders'[BilledNetProductSales]),

            FILTER('Billing Date','Billing Date'[Billing Date] = CurrentDate - 1)  

        )

    )

RETURN SalesPriorDays

[lbendlin · Accepted solution]

I would use the WINDOW function to decide if I should look back to the previous day, the previous two days or the previous three days. Much simpler to implement.