topic using time intelligence (dates between) inside summarize function in DAX Commands and Tips

using time intelligence (dates between) inside summarize function

Amir_Meidan — Wed, 15 Jan 2025 16:57:48 GMT

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

Re: using time intelligence (dates between) inside summarize function

Anonymous — Thu, 16 Jan 2025 06:37:41 GMT

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.