using time intelligence (dates between) inside summarize function

[Amir_Meidan]

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 · Accepted solution]

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.