topic Re: How to subtract 1 day from the my budget dax allocation in DAX Commands and Tips

How to subtract 1 day from the my budget dax allocation

dakins — Mon, 13 Feb 2023 16:03:29 GMT

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

Re: How to subtract 1 day from the my budget dax allocation

tamerj1 — Mon, 13 Feb 2023 16:31:07 GMT

dakins

Please try

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

Re: How to subtract 1 day from the my budget dax allocation

dakins — Tue, 14 Feb 2023 16:20:05 GMT

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

Re: How to subtract 1 day from the my budget dax allocation

tamerj1 — Tue, 14 Feb 2023 16:50:00 GMT

dakins

How about

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

Re: How to subtract 1 day from the my budget dax allocation

dakins — Tue, 14 Feb 2023 19:56:32 GMT

Thank you tamerj1 !