Hi all,
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
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
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.
I have tried everything but nothing seems to work. Can I email you with more details?
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.
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.
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
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])))))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.
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.
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])))))Side note - please format the formula correctly for forum posts. See here - https://forum.enterprisedna.co/t/how-place-dax-formula-into-forum-topics-posts/156/3
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.
Hello. I found this post through a Google search and this is exactly what I need. I have copied the above formula, and changed the names of columns and measures to fit my PowerBI. However, I am not receiving the desired result.
Below is my formula.
Total Sales Non Weekend Days =
VAR WeekendCheck = OR ( SELECTEDVALUE(‘Calendar’[Day Type]) = “Weekend”, SELECTEDVALUE(‘Calendar’[Day Type]) = “Holiday”)
VAR WorkdayCheck = SELECTEDVALUE(‘Calendar’[Day Type]) = “Work Day”
RETURN
IF( WeekendCheck = True, BLANK(),
IF( WorkdayCheck = FALSE,
[SO Total Extended Amount],
CALCULATE( [SO Total Extended Amount],
FILTER( ALL( ‘Calendar’), ‘Calendar’[Date] > MIN( ‘Calendar’[Date] ) - 3 && ‘Calendar’[Date] <= MIN( ‘Calendar’[Date]))
)
)
)
Again, same as the one in the original post, but changes made to fit my model. I am not able to get past the Logic 1 in the IF Statement,
IF( WeekendCheck = True, BLANK(),
My Calendar table does in fact have a Day Type column with “Weekend”, “Holiday”, and “Work Day” as values. However, my table this is not returning Blank() values.
Please let me know if I need to send my file for further review.
Thanks in advance.
Hi @PatrickKelly1,
Please start a new topic as this topic is already solved. Members of the forum doesn’t review the topics that are already solved/closed.
thanks
Keith