Roll Sat/Sun Sales into Friday while taking into consideration the overall month

[Anonymous]

I am working on a daily report of operations visual, I have a great formula that puts Saturday and Sunday's Sales into Friday BUT...... It does not take into consideration month end. September to October of this year (2023) is a perfect example. October 1st is a Sunday. Currently my report is showing October 1st sales on Friday Sept. 29th business day. Obviously from a financial perspective this is a huge problem. I am needing help with some kind of a formula that puts Sat/Sun sales into Friday AND also looks at what day of the month it is to ensure the sales hit the proper month. Below is my current formula.... THANK YOU!!!!

Sales Revenue Roll Into Friday =

VAR _SalesRevenue =

SUM ( 'SalesNet'[SalesRevenue] )

RETURN

IF (

HASONEVALUE ( Dimdate[FullDateAlternateKey] ),

SUMX (

VALUES ( 'DimDate'[FullDateAlternateKey] ),

IF (

WEEKDAY ( 'DimDate'[FullDateAlternateKey] ) = 6,

_SalesRevenue

+ CALCULATE (

SUM ( 'SalesNet'[SalesRevenue] ),

NEXTDAY ( Dimdate[FullDateAlternateKey] ))

+ CALCULATE (

SUM ( 'SalesNet'[SalesRevenue] ),

NEXTDAY ( NEXTDAY ( DimDate[FullDateAlternateKey] ) )),

IF ( WEEKDAY ( 'DimDate'[FullDateAlternateKey] ) IN { 1, 7 }, BLANK (), _SalesRevenue ))),

_SalesRevenue)

[Anonymous]

This doesn't quite fit my needs. The first can't be blank. This is used for accounting purposes that would mean that we are missing the first's sales revenue then from October

[123abc]

I understand the requirements better now. If you don't want to return blank for the first day of the month, you can modify the formula to check if rolling the sales to the next day would go to the next month, and if so, allocate the sales to the last day of the current month. I have updated the formula as follows:

Sales Revenue Roll Into Friday =
VAR _SalesRevenue =
SUM ( 'SalesNet'[SalesRevenue] )
RETURN
IF (
HASONEVALUE ( Dimdate[FullDateAlternateKey] ),
SUMX (
VALUES ( 'DimDate'[FullDateAlternateKey] ),
IF (
WEEKDAY ( 'DimDate'[FullDateAlternateKey] ) = 6,
IF (
MONTH ( 'DimDate'[FullDateAlternateKey] )
<> MONTH ( NEXTDAY ( Dimdate[FullDateAlternateKey] ) ),
_SalesRevenue
+ CALCULATE (
SUM ( 'SalesNet'[SalesRevenue] ),
NEXTMONTH ( Dimdate[FullDateAlternateKey] ) - 1
)
+ CALCULATE (
SUM ( 'SalesNet'[SalesRevenue] ),
NEXTMONTH ( Dimdate[FullDateAlternateKey] ) - 2
),
_SalesRevenue
+ CALCULATE ( SUM ( 'SalesNet'[SalesRevenue] ), NEXTDAY ( Dimdate[FullDateAlternateKey] ) )
+ CALCULATE ( SUM ( 'SalesNet'[SalesRevenue] ), NEXTDAY ( NEXTDAY ( DimDate[FullDateAlternateKey] ) ) )
),
IF (
WEEKDAY ( 'DimDate'[FullDateAlternateKey] ) IN { 1, 7 },
BLANK (),
_SalesRevenue
)
)
),
_SalesRevenue
)

This modification checks if rolling the sales to the next day would go to the next month. If so, it allocates the sales to the last day of the current month. Otherwise, it rolls the sales to the next day as before. Please test it thoroughly with your data to ensure it meets your accounting requirements.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[Anonymous]

I attempted to input the above and got the below error

"A function 'NEXTMONTH' has been used in a True/False expression that is used as a table filter expression. This is not allowed"

[123abc]

Please try the updated formula below:

Sales Revenue Roll Into Friday =
VAR _SalesRevenue =
SUM('SalesNet'[SalesRevenue])
RETURN
IF (
HASONEVALUE('DimDate'[FullDateAlternateKey]),
SUMX(
VALUES('DimDate'[FullDateAlternateKey]),
IF (
WEEKDAY('DimDate'[FullDateAlternateKey]) = 6,
_SalesRevenue
+ CALCULATE(
SUM('SalesNet'[SalesRevenue]),
NEXTDAY('DimDate'[FullDateAlternateKey])
)
+ CALCULATE(
SUM('SalesNet'[SalesRevenue]),
NEXTDAY(NEXTDAY('DimDate'[FullDateAlternateKey]))
),
IF (
WEEKDAY('DimDate'[FullDateAlternateKey]) IN { 1, 7 },
IF (
MONTH('DimDate'[FullDateAlternateKey])
= MONTH(
NEXTDAY('DimDate'[FullDateAlternateKey])
),
_SalesRevenue,
BLANK()
),
_SalesRevenue
)
)
),
_SalesRevenue
)

This should address the error you encountered. Please give it a try, and let me know if it meets your requirements or if further adjustments are needed.

[Anonymous]

The forumla worked and is showing values. However, none of the days are combining. It is still showing the full month 10/1-10/31 10/1 is a Sunday and should be combined with 10/2

[123abc]

I see, and I appreciate your patience. Let's make further adjustments to ensure the correct combination of Saturday and Sunday with Friday. The issue might be related to the context in which the calculations are performed. Here's a modified version of the formula:

Sales Revenue Roll Into Friday =
VAR _SalesRevenue =
SUM('SalesNet'[SalesRevenue])
RETURN
IF (
HASONEVALUE(DimDate[FullDateAlternateKey]),
SUMX (
VALUES(DimDate[FullDateAlternateKey]),
IF (
WEEKDAY(DimDate[FullDateAlternateKey]) = 6,
_SalesRevenue
+ CALCULATE(
SUM('SalesNet'[SalesRevenue]),
FILTER(
ALL(DimDate),
DimDate[FullDateAlternateKey] = DimDate[FullDateAlternateKey] + 1
)
)
+ CALCULATE(
SUM('SalesNet'[SalesRevenue]),
FILTER(
ALL(DimDate),
DimDate[FullDateAlternateKey] = DimDate[FullDateAlternateKey] + 2
)
),
IF (
WEEKDAY(DimDate[FullDateAlternateKey]) IN {1, 7},
_SalesRevenue,
BLANK()
)
)
),
_SalesRevenue
)

This modification includes the FILTER function to explicitly specify the conditions for adding sales from the next days. It uses the ALL function to disregard any existing filters on the date column.

Please try this adjusted formula, and it should ensure that Saturday and Sunday's sales are combined with Friday while maintaining the correct month boundaries.

[123abc]

Pleaese try this modified DAX:

Sales Revenue Roll Into Friday =
VAR _SalesRevenue =
SUM ( 'SalesNet'[SalesRevenue] )
RETURN
IF (
HASONEVALUE ( Dimdate[FullDateAlternateKey] ),
SUMX (
VALUES ( 'DimDate'[FullDateAlternateKey] ),
IF (
WEEKDAY ( 'DimDate'[FullDateAlternateKey] ) = 6,
IF (
DAY ( 'DimDate'[FullDateAlternateKey] ) = 1, -- Check if it's the first day of the month
BLANK (),
_SalesRevenue
+ CALCULATE (
SUM ( 'SalesNet'[SalesRevenue] ),
NEXTDAY ( Dimdate[FullDateAlternateKey] )
)
+ CALCULATE (
SUM ( 'SalesNet'[SalesRevenue] ),
NEXTDAY ( NEXTDAY ( DimDate[FullDateAlternateKey] ) )
)
),
IF (
WEEKDAY ( 'DimDate'[FullDateAlternateKey] ) IN { 1, 7 },
BLANK (),
_SalesRevenue
)
)
),
_SalesRevenue
)

This modification includes an additional check for the first day of the month. If it's the first day of the month and a Sunday, it will return BLANK(), effectively excluding the sales from the previous month. Adjust this as needed based on your specific requirements.

Please replace this adjusted formula in your report and test it thoroughly to ensure it meets your expectations.

[Anonymous]

Close, now it is only showing weekends