Total sales sum to next business day


#1

Hi Sam,
I am trying to calculate Total sales for business weeks. We have a couple of dollar sales on holidays that I would like to count on next business day. Trying to figure out the best way to accomplish that. I have working days column available in the date table but I am not sure how can I make sure the sales gets added to the next business day.
Any help would be appreciated.

Kiran


#2

Have you been able to go through some of the below content?

This one shows a technique where you can move data from one day to the next

If this doesn’t cover it I would need to a specific example with the formulas you already have


#3

Hi Sam,
The second video (Dealing with Multiple Currencies in Power BI) gets me closer to the solution but not there yet.
I have a table with our workday sales but there is some amount is showing in off days (by our branch in Asia) which I ideally would like to count on our next business day.
For example, I want to show 1.25 added to the next workday.

image


#4

I have tried everything but nothing seems to work. Can I email you with more details?


#5

I’m a bit lost at the moment around what you need here.

I need it laid out in a bit more detail, with DAX formulas you currently have included.

I feel like I’m trying to work something out here without all the information.

If you need me to look at a model you can actually attach this in the forum post.


#6

Hi Sam,

Let me explain the reason I want nonworking days sales added in next business days is that when I am using total sale amount to get rolling average or presenting a sales trending chart some sales amount converted on a nonworking day makes my averages trend line drops. My model for this report is very simple but I am trying to group any nonworking days sales to next business day. I apologize if I am still confusing you. Please let me know.


#7

Ok this is how you would need to do it.

You would need to change your core Sales calculation to skip weekends.

This is how I have done it. You may need to rearrange based on your requirement

Total Sales Non Weekend Days = 
VAR WeekendCheck = SELECTEDVALUE( Dates[Day Type] ) = "Weekend"
VAR WorkdayCheck = SELECTEDVALUE( Dates[DayOfWeekName] ) = "Monday"

RETURN
IF( WeekendCheck = TRUE, BLANK(),
    IF( WorkdayCheck = FALSE,
        [Total Sales],
            CALCULATE( [Total Sales],
                FILTER( ALL( Dates ), Dates[Date] > MIN( Dates[Date] ) - 3 && Dates[Date] <= MIN( Dates[Date] ) ) ) ) )

This is the result, where I have push weekend number to the first day of the working week, which is Monday


#8

It worked great! There few things I did different. Instead of “Monday” I used “Work Day” (I have company holidays imported from sharepoint). It seems to work very well. Thank you for your help!

Total Sales Non Holiday =
VAR Weekendcheck = SELECTEDVALUE(‘Date’[Holidays & WorkDays]) = “Off Day”
VAR WorkdayCheck = SELECTEDVALUE(‘Date’[Holidays & WorkDays]) = “Work Day”

Return
IF( Weekendcheck = TRUE(), Blank(),
IF(WorkdayCheck = FALSE,
[Total Sales],
CALCULATE([Total Sales],
FILTER(ALL(‘Date’), ‘Date’[Date] > MIN(‘Date’[Date])-3 && ‘Date’[Date] <= MIN(‘Date’[Date])))))


#9

Hi Sam, It works well with “weekend” but it doesn’t work with “holidays/off Day”. We had Memorial day on Monday 28th and Christmas Holiday on Friday so I was trying to see if I can use “work day” instead of “Monday” to avoid that. The numbers are random It’s not showing right results.
Is there any way I can replace “-3” in the formula to next business day.


#10

What you would need to do in this case then is the include that holiday logic into this part of the formula

VAR WorkdayCheck = SELECTEDVALUE(‘Date’[Holidays & WorkDays]) = “Work Day”

You may need to do something like this

VAR WorkdayHolidayCheck = OR ( SELECTEDVALUE(‘Date’[Holidays & WorkDays]) = “Work Day”, SELECTEDVALUE(‘Date’[Holidays Column]) = “Holiday”

That it really. Then the same formula should work fine.


#11

Doesn’t seem to work. The numbers I get are large.
Here is my formula

Total sales (Holidays) =
VAR Weekendcheck = SELECTEDVALUE(‘Date’[Holidays & WorkDays]) = “off Day”
VAR WorkdayHolidayCheck = OR(SELECTEDVALUE(‘Date’[Holidays & WorkDays]) = “work Day”, SELECTEDVALUE(‘Date’[Holidays & WorkDays]) = “off Day”)
Return
IF(Weekendcheck = TRUE(), BLANK(),
IF(WorkdayHolidayCheck = FALSE(),
[Total Sales], CALCULATE([Total Sales], FILTER(ALL(‘Date’), ‘Date’[Date] > MIN(‘Date’[Date]) -3 && ‘Date’[Date] <= MIN(‘Date’[Date])))))


#12

Side note - please format the formula correctly for forum posts. See here - How Place DAX Formula Into Forum Topics & Posts


#13

This should work if it is using the logic I have described.

I can’t really tell what the issue it if no detail is included in the post. Please be more descriptive and add images of current results and columns you have created.