cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Regular Visitor

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

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)
8 REPLIES 8
Regular Visitor

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

Community Champion

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.

Regular Visitor

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"

Community Champion

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.

Regular Visitor

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

Community Champion

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.

Community Champion

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.

Regular Visitor

Close, now it is only showing weekends

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors