Move data from Weekend to Friday

[SimplesHoT89]

Hi guys,

I have a quite simple data table with the following issue. (date column and then a column for each product)

The data shows the production numbers on a daily basis, however some saturdays have values as well and I want to move the data from all Saturdays to the weekday before, therefore Friday because it's messing up my daily average. I'm struggling to find the right function so any help is appreciated.

Thanks!

[v-rongtiep-msft · Accepted solution]

Hi @SimplesHoT89 ,

I have created a simple sample, please refer to it to see if it helps you.

Create three columns (must use columns).

ALLVALUE =
'Table'[A] + 'Table'[B] + 'Table'[C] + 'Table'[D] + 'Table'[E] + 'Table'[F]

WEEKDAY = WEEKDAY('Table'[date],2)

WEEKNO = WEEKNUM('Table'[date],2)

Then the result column you can use a measure or a column.

A measure.

Me =
VAR _WEEKEND =
    CALCULATE (
        SUM ( 'Table'[ALLVALUE] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[WEEKNO] = SELECTEDVALUE ( 'Table'[WEEKNO] )
                && 'Table'[WEEKDAY] >= 6
        )
    )
VAR _VALUE =
    CALCULATE (
        MAX ( 'Table'[ALLVALUE] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[WEEKDAY] = 5
                && 'Table'[WEEKNO] = SELECTEDVALUE ( 'Table'[WEEKNO] )
        )
    )
RETURN
    IF (
        MAX ( 'Table'[WEEKDAY] ) = 5,
        _VALUE + _WEEKEND,
        IF ( MAX ( 'Table'[WEEKDAY] ) >= 6, BLANK (), MAX ( 'Table'[ALLVALUE] ) )
    )

 A column.

Me_COLUN =
VAR _WEEKEND =
    CALCULATE (
        SUM ( 'Table'[ALLVALUE] ),
        FILTER (
            ( 'Table' ),
            'Table'[WEEKNO] = EARLIER ( 'Table'[WEEKNO] )
                && 'Table'[WEEKDAY] >= 6
        )
    )
VAR _VALUE =
    CALCULATE (
        SUM ( 'Table'[ALLVALUE] ),
        FILTER (
            ( 'Table' ),
            'Table'[WEEKDAY] = 5
                && 'Table'[WEEKNO] = EARLIER ( 'Table'[WEEKNO] )
        )
    )
RETURN
    IF (
        ( 'Table'[WEEKDAY] ) = 5,
        _VALUE + _WEEKEND,
        IF ( ( 'Table'[WEEKDAY] ) >= 6, BLANK (), ( 'Table'[ALLVALUE] ) )
    )

If I have misunderstood your meaning, please provide more details with your desired output.

Best Regards

Community Support Team _ Polly

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

[SimplesHoT89]

Hi @v-rongtiep-msft,

thanks for the answer. My second Screenshot was maybe a bit misleading to be fair. I do need the values from the weekend to be added to the total of the product (therefore in your example column A, B,...)  But i can simply adjust what you've posted.

Thanks a lot!