Forum Discussion

Amir_Meidan's avatar
Amir_Meidan
Frequent Visitor
1 year ago
Solved

using time intelligence (dates between) inside summarize function

Hello,
I use this formula to generate table where i have for each date and account from the originl table a new column with last 28 days number of conversations.

table =
SUMMARIZE(
AI_AUTO_PILOT_WITH_TICKET_INFO,
DIM_TIME[DATE],
DIM_ACCOUNT[ACCOUNT_ID],
"last_28_conv_account",
CALCULATE(
DISTINCTCOUNT(AI_AUTO_PILOT_WITH_TICKET_INFO[CONVERSATION_ID]),
DATESBETWEEN(
DIM_TIME[DATE],
MIN(DIM_TIME[DATE]) - 28, -- Use MIN(DIM_TIME[DATE]) to get the current row's date context
MIN(DIM_TIME[DATE]) - 1 -- The last day before the current row's date
)
)
)


why does it returns empty when i use it in a table with date and account columns included?
when i switch to today() its working but i want it to be related to filter context of date and be evaluated for each date sepratly.
thank you 

  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Amir_Meidan ,

     

    Here is the modified formula:

    table = ADDCOLUMNS(
SUMMARIZE(
AI_AUTO_PILOT_WITH_TICKET_INFO,
DIM_TIME[DATE],
DIM_ACCOUNT[ACCOUNT_ID]),
"last_28_conv_account",
VAR _current='DIM_TIME'[Date]
RETURN
CALCULATE(
DISTINCTCOUNT(AI_AUTO_PILOT_WITH_TICKET_INFO[CONVERSATION_ID]),
'AI_AUTO_PILOT_WITH_TICKET_INFO','AI_AUTO_PILOT_WITH_TICKET_INFO'[DATE]>=_current - 28, -- Use MIN(DIM_TIME[DATE]) to get the current row's date context
'AI_AUTO_PILOT_WITH_TICKET_INFO'[DATE]<_current -- The last day before the current row's date
)
)

    The ADDCOLUMNS function was used to add a calculated column to the above formula, and a date variable was created to specify the row context.

     

    Result:

     

    Best Regards,
    Zhu

     

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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Amir_Meidan ,

     

    Here is the modified formula:

    table = ADDCOLUMNS(
SUMMARIZE(
AI_AUTO_PILOT_WITH_TICKET_INFO,
DIM_TIME[DATE],
DIM_ACCOUNT[ACCOUNT_ID]),
"last_28_conv_account",
VAR _current='DIM_TIME'[Date]
RETURN
CALCULATE(
DISTINCTCOUNT(AI_AUTO_PILOT_WITH_TICKET_INFO[CONVERSATION_ID]),
'AI_AUTO_PILOT_WITH_TICKET_INFO','AI_AUTO_PILOT_WITH_TICKET_INFO'[DATE]>=_current - 28, -- Use MIN(DIM_TIME[DATE]) to get the current row's date context
'AI_AUTO_PILOT_WITH_TICKET_INFO'[DATE]<_current -- The last day before the current row's date
)
)

    The ADDCOLUMNS function was used to add a calculated column to the above formula, and a date variable was created to specify the row context.

     

    Result:

     

    Best Regards,
    Zhu

     

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