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

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.