Calculate weekend sales into weekdays(closest working day in the current month)

[Shravan133]

Hi,

I have a sales column with everyday sales. I want to move the weekend sales to the closest working day in the current month. 

For example:

if saturday and sunday sales are in the same month, the sales should be added to the previous friday sales.

if 30th november sales is on saturday, i want the sales to be added to 29th of november which is friday. if 1st december sales is on sunday, i want the sales to be added to 2nd december which is monday. so ideally, weekend sales should be added to the closest working day in the same month. I hope its clear.

Adjusted sales measure:

Adjusted Sales =SUMX(VALUES('Date'[Date]), CALCULATE(
SUM('Actual Sales'[Sales(MT)]),
TREATAS(VALUES('Date'[ClosestWorkingDay]),
'Actual Sales'[Date_Date])))

Expected result in the adjusted sales column in table:

2/12/2024 = sales from 1st dec(sunday) + 2nd dec(monday)(169.49 + 6.85)

29/11/2024 = sales from 29th nov(friday) + 30 th nov(saturday)(102.87 + 32.87)

weekend rows should be empty and added to the closest working day.

I hope i have been clear. Any help is much appreciated.

[powerbiexpert22 · Accepted solution]

Hi @Shravan133 ,

please try below, please find attached pbix for your reference

https://drive.google.com/file/d/1nonw7bT7MSLTQ-Ai63ka4t6BP3Z05ESz/view?usp=drive_link

closestworkingday =

var last_day_of_month=EOMONTH(Sheet1[Date],0)

RETURN

if( Sheet1[Date]=last_day_of_month && FORMAT(Sheet1[Date],"DDD")="Sun",Sheet1[Date]-2,

if(Sheet1[Weekday]="Sat",Sheet1[Date]-1,if(Sheet1[Weekday]="Sun",Sheet1[Date]+1,

Sheet1[Date])))

sale adjusted =

var a = CALCULATE(MAX(Sheet1[Sales]),FILTER(ALL(Sheet1),Sheet1[Date]=max(Sheet1[closestworkingday])))

RETURN

if (max(Sheet1[Weekday])="Sat" || max(Sheet1[Weekday])="Sun" ,a+SUM(Sheet1[Sales]),SUM(Sheet1[Sales]))

[v-aatheeque]

Hi @Shravan133 ,

we haven't heard back from you regarding our last response and wanted to check if your issue has been resolved.

If our response addressed your query, please mark it as Accept Answer and click Yes if you found it helpful.

Should you have any further questions, feel free to reach out.
Thank you for being a part of the Microsoft Fabric Community Forum!

[v-aatheeque]

Hi @Shravan133 
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet.
In case if you have any resolution please do share that same with the community as it can be helpful to others.
Otherwise, will respond back with the more details and we will try to help.

[v-aatheeque]

Hi @Shravan133 

we haven't heard back from you regarding our last response and wanted to check if your issue has been resolved.

If our response addressed your query, please mark it as Accept Answer and click Yes if you found it helpful.

Should you have any further questions, feel free to reach out.
Thank you for being a part of the Microsoft Fabric Community Forum!

[Ronak131290]

Dear Shravan133

Preview

Create a Two Custom Column one is AdustedDate and Day of Week

1 Adjusted Date =

SWITCH(

    WEEKDAY(WB_PrimarySales[Date], 2),

    6, WB_PrimarySales[Date] - 1,  -- Saturday becomes Friday

    7, WB_PrimarySales[Date] + 1,  -- Sunday becomes Monday

    WB_PrimarySales[Date]          -- All other days remain unchanged

)

2. Day of Week = FORMAT(WB_PrimarySales[Date], "dddd")

Create a Measure for AdjustedSale

3. Adjusted Sale =

IF(

    SELECTEDVALUE(WB_PrimarySales[Day of Week]) IN {"Saturday","Sunday"},

    BLANK(),

    CALCULATE(

    SUM(WB_PrimarySales[Sale]),

    ALLEXCEPT(WB_PrimarySales , WB_PrimarySales[Adjusted Date])

)

)

If You have an any douts then tell me.

If Solution satisfy with you query then accepted as a solution.
Regards, 
Ronak Mistry

[AMeyersen]

You can add the closest working date as a calculated column to your fact table 'Actual Sales':

ClosestWorkingDay =
VAR _weekday =
    WEEKDAY ( 'ActualSales'[Date] )
VAR _adjustDay =
    IF (
        _weekday = 7,
        IF ( MONTH ( 'ActualSales'[Date] ) = MONTH ( 'ActualSales'[Date] - 1 ), -1, 2 ),
        IF (
            _weekday = 1,
            IF ( MONTH ( 'ActualSales'[Date] ) = MONTH ( 'ActualSales'[Date] + 1 ), 1, -2 ),
            0
        )
    )
RETURN
    DATEADD ( 'ActualSales'[Date], _adjustDay, DAY )

Then you could add an inactive relationship between your date table and the new column ClosestWorkingDay and calculate the new measure:

AdjustedSales =
CALCULATE (
    SUM ( ActualSales[Sales(MT)] ),
    USERELATIONSHIP ( ActualSales[ClosestWorkingDay], 'Date'[Date] )
)

or if [ClosestWorkingDay] is in the 'Date' table, the inactive relationship is between 'Date'[ClosestWorkingDay] and Actual Sales'[Date] and the measure is

AdjustedSales =
CALCULATE (
    SUM ( ActualSales[Sales(MT)] ),
    USERELATIONSHIP ( 'Date'[ClosestWorkingDay], 'Actual Sales'[Date] )
)​

 Or you calculate the measure without a new relationship like:

AdjustedSales =
VAR _selectedDates =
    VALUES ( 'Date'[Date] )
RETURN
    CALCULATE (
        SUM ( ActualSales[Sales(MT)] ),
        ActualSales[ClosestWorkingDay] IN _selectedDates,
        REMOVEFILTERS ( 'Date' )
    )