Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Solving calcuated Column problem using dax

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.




"CALCULATE"
"Calculated Column"
DAX
EARLIER
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    hi Anonymous 

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

  • FreemanZ's avatar
    FreemanZ
    3 years ago

    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:

     

     

7 Replies

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    hi Anonymous 

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    FreemanZ 

    In this SS the Daily_Split_Final is my Expected column with values

    • FreemanZ's avatar
      FreemanZ
      Super User
      3 years ago

      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's avatar
        Anonymous
        Not applicable
        3 years ago

        Thanks a ton FreemanZ 

        This really worked 😍
        Can you teach me please!