Solving calcuated Column problem using dax

[Anonymous]

I am facing an issue with calculated column.

This is sample data and here I want to achieve if isStoreClosed is Closed Status then I want to divide the Daily_Split Value by 6 and add this average value to the next 6 Retail Dates Daily_Split Value.

I have created the Daily_Split2 Column and written the folloing dax to achieve the result

Daily_split 2 =

VAR Closed =

    CALCULATE (

        SUM ( 'Daily Table'[Daily_Split] ) / 6,

        FILTER (

            'Daily Table',

            'Daily Table'[IsStoreClosed] = "Closed"

                && 'Daily Table'[Store_Code] = EARLIER ( 'Daily Table'[Store_Code] )

                && 'Daily Table'[Retail Date] = EARLIER ( 'Daily Table'[Retail Date])

                && 'Daily Table'[Iteration] = EARLIER('Daily Table'[Iteration])

                && 'Daily Table'[Category]= EARLIER('Daily Table'[Category])))

   return

    IF (

        'Daily Table'[IsStoreClosed] = "Open",

        CALCULATE (

            SUM('Daily Table'[Daily_Split])+ Closed,

            FILTER (

                'Daily Table',

                'Daily Table'[Store_Code] = EARLIER('Daily Table'[Store_Code])

                && 'Daily Table'[Retail Date] >= EARLIER('Daily Table'[Retail Date])

                && 'Daily Table'[Retail Date] <= EARLIER('Daily Table'[Retail Date]) + 6

                && 'Daily Table'[Iteration] = EARLIER('Daily Table'[Iteration])

                && 'Daily Table'[Category]=EARLIER('Daily Table'[Category])

            )

        ))

But this Code not giving me the desired result. I am not able to add the average value to the next 6 days.
How do I fix this any help please.

[FreemanZ · Accepted solution]

hi @Anonymous 

It would be good if you could simplify your case and provide the value of your expected column directly. 

[Anonymous]

@FreemanZ 

In this SS the Daily_Split_Final is my Expected column with values

[FreemanZ · Accepted solution]

hi @Anonymous 

try to add a column like:

Column2 = 
VAR _lastclosedsplit =
MAXX(
    TOPN(
        1,
        FILTER(
            TableName,
            TableName[Date]<EARLIER(TableName[Date])
                &&TableName[IsClosed]="Closed"
        ),
        TableName[Date]
    ),
    TableName[DailySplit] 
)
RETURN
IF(
    [IsClosed] = "Closed",
    BLANK(),
    [DailySplit] + DIVIDE(_lastclosedsplit, 6 )
)

or

Column = 
VAR _lastcloseddate =
MAXX(
    FILTER(
        TableName,
        TableName[Date]<EARLIER(TableName[Date])
            &&TableName[IsClosed]="Closed"
    ),
    TableName[Date]
)
VAR _lastclosedsplit =
MAXX(
    FILTER(
        TableName,
        TableName[Date]=_lastcloseddate
    ),
    TableName[DailySplit]
)
RETURN
IF(
    [IsClosed] = "Closed",
    BLANK(),
    [DailySplit] + DIVIDE(_lastclosedsplit, 6 )
)

it worked like:

[Anonymous]

How to solve this if the DailySplit is not the max number for closed dates @FreemanZ  ??

[FreemanZ]

hi @Anonymous 

it shall still work, as MAXX here is actually calculating the max on a single value itself, filtered by the first argument of MAXX. Just give it a try.

[Anonymous]

Thanks a ton @FreemanZ 

This really worked 😍
Can you teach me please! 

[Anonymous]

Hi @FreemanZ , So in this screenshot if you see I have created a Daily_split 2 where I have divided the Value of Daily_split by 6 (Avg_Value) where isStoreClosed is in "Closed" Status which is Sunday
And now I wanted to add this value (Avg_Value)  with the Daily_split value to the next 6 retail dates where the store is Open status and leave the row blank for "Closed" dates.

[FreemanZ · Accepted solution]

hi @Anonymous 

It would be good if you could simplify your case and provide the value of your expected column directly.