How to subtract 1 day from the my budget dax allocation

[dakins]

Hello all,

Below is my dax code for allocating expected budget goals evenly throughout the year. However, my sales data in updated to power bi a day late. This means my allocation is the expected budget for today, but my sales data is for yesterday, not today. 

How can I subtract one day from my budget allocation, so allocation matches the sales data

Budget_23' =

var __datesV = values( 'Calendar'[Date] )

var __result =

    sumx(

        values( 'Calendar'[MonthYear] ),

        CALCULATE(

            var __monthlyBudget = SUM('23_Budget'[Budget_Amount] )

            var __workingDaysPerMonth = [Working_Days]

            var __workingDaysPerMonthInSelection =

                calculate(

                    [Working Days],

                    keepfilters( __datesV )

                )

            var __budgetInMonthForSelection =

                divide(

                    __monthlyBudget * __workingDaysPerMonthInSelection,

                    __workingDaysPerMonth

                )

            return

                __budgetInMonthForSelection,

            ALLEXCEPT( 'Calendar', 'Calendar'[MonthYear] )

        )

    )

return

    __result

Thank you

[dakins]

Thank you @tamerj1 !

[tamerj1]

@dakins

Please try

VAR __datesV =
DATESBETWEEN (
'Calendar'[Date],
MIN ( 'Sales Actual'[Order Date] ),
MAX ( 'Sales Actual'[Order Date] )
)

[dakins]

Hello @tamerj1,

Thanks for the solution. While your solution should work in most cases, it won't work in mine. My report has a few data that spills in from the current day. 

Example - the report was initated at 12:00 AM and take 15 mins to create and export to the server. We sometime get sales data in that time for the current date usually less than 100 records but I'd like to exclude them totally hence I cannot use MAX ( 'Sales Actual'[Order Date] ).

Let me know if you have any suggestions.


My thanks,
Dan

[tamerj1 · Accepted solution]

@dakins 

How about 

VAR __datesV =
DATESBETWEEN (
'Calendar'[Date],
MIN ( 'Sales Actual'[Order Date] ),
MIN ( MAX ( 'Sales Actual'[Order Date] ), TODAY ( ) - 1 )
)