Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dakins
Helper II
Helper II

How to subtract 1 day from the my budget dax allocation

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

1 ACCEPTED SOLUTION

@dakins 

How about 

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

View solution in original post

4 REPLIES 4
dakins
Helper II
Helper II

Thank you @tamerj1 !

tamerj1
Super User
Super User

@dakins

Please try

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

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

@dakins 

How about 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors