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 aver...
"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:

     

     

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
Icon for Super User rankSuper 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! 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

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

    • FreemanZ's avatar
      FreemanZ
      Icon for Super User rankSuper User
      3 years ago

      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.