DAX optimization of measure that measures the mean of days between transactions

[phorfanos]

I have created 2 measures,
the first one calculates the days between to 2 consecutive transactions:
DaysBetweenTransactionDates =

VAR CurrentDate = MAX(Tickets[Transaction Date])

VAR PriorDate = CALCULATE(MAX(Tickets[Transaction Date]), FILTER(ALLSELECTED(Tickets), Tickets[Transaction Date] <CurrentDate))

RETURN

IF(CurrentDate = CALCULATE(MIN(Tickets[Transaction Date]), ALLSELECTED(Tickets)),

0,

CurrentDate - PriorDate)

and the second one takes the mean of the previous measure as follows:

DaysBetweenTransactionDates Mean =

AVERAGEX(VALUES(Tickets[Transaction Date]), [DaysBetweenTransactionDates])

When I place the 2nd measure in a card it doesn't take much to visualise but when I place the m2nd measure in a table with all agents it took more than 5 minutes to visualise the mean days for all agents. 
Consider that it reads a table with ~70 million rows. 
What should I do to optimize the measures in order to be loaded quickly?

[Daniel29195 · Accepted solution]

hello @phorfanos 

can you please check this measure : 

link to download file from : 

https://drive.google.com/file/d/1FI-fqw3eB5vGo9lA1dgTzfRR-Vi3adCz/view?usp=sharing

summary : 
1.create dimdate

2.create dimagent

3.link both tables to Table4 (the table that contains the transactional data ) 

4. write the measure :   days since next trans - measure ( this calculates the average ) 

model  :

output : 

days since next trans - measure = 
 var current_date =  MAX(Table4[Transaction Date])
var c =  CALCULATE(COUNT(Table4[Transaction Date]) )

var datasource = 
CALCULATETABLE(
    SUMMARIZE(
        table4,
        dimagent[Agent],
        dimdate[Date]
    ),
        ALL(dimdate),
        dimdate[Date]  <=current_date
)

var datasource1 = 
CALCULATETABLE(
    SUMMARIZE(
        table4,
        dimagent[Agent],
        dimdate[Date]
    ),
        ALL(dimdate),
        dimdate[Date]  <=current_date
    )


var datasource2 = 
ADDCOLUMNS(
ADDCOLUMNS(
    datasource1,
    "prev_date", 
    SELECTCOLUMNS(
        offset(
            1, 
            ,ORDERBY(dimdate[Date], desc)
        ),
    "prev_date",  dimdate[Date]
    )
),
"diff", DATEDIFF([prev_date],  dimdate[Date],DAY) 
)

var res = SUMx(datasource2 , [diff]) / c

return if( current_date in VALUES(Table4[Transaction Date]), res)

let ne know if it works for you .

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅!

It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

[Daniel29195]

hello @phorfanos 

are you able to show at least a sample data for 1 transaction with the table sturcture, (a screenshot would be enough just to make sense of what you want to achieve.

[phorfanos]

Here an example for 1 agent A with 9 transactions during the last year: 

Transaction Date	Days since next transaction
7/31/2023	0
8/11/2023	11
8/14/2023	3
8/16/2023	2
8/17/2023	1
8/19/2023	2
8/30/2023	11
9/5/2023	6
9/15/2023	10

For example it took 11 days to retransact the first time (from 31st July 2023 to 11th August 2023. And then the mean days for transacton is equal to 5.11. On average, this agent makes a transaction every 5.11 days. I hope it is clearer now. 

When I insert the second measure (mean) in a table with all agents, it takes a long time to get results. Ideally I would like to have the following table: 

Agent	Mean of days between transactions
A	5.11
B	?
C	?
...	...

[v-tianyich-msft]

Hi @phorfanos ,

I have created simple samples and you can check the results below:

Measure = VAR _T = ADDCOLUMNS('Table',"AVG",AVERAGEX(FILTER(ALL('Table'),[Type]=EARLIER([Type])),[Days since next transaction]))
RETURN MAXX(_T,[AVG])

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[phorfanos]

Thank you for your reply! Actually my formula (

DaysBetweenTransactionDates Mean =

AVERAGEX(VALUES(Tickets[Transaction Date]), [DaysBetweenTransactionDates]))
works fine and gives the results that you also reproduced. The problem is that it takes a long time to give the means for all agents (over 3mins), while when I select only one agent, I get the result quite fast (either in the table or in a separate card). 
So, I think the problem is in the first variable ("DaysBetweenTransactionDates", please refer to my initial post). @Daniel29195 suggested a debug of the code, so I tried the following

DaysBetweenTransactionDates =

VAR CurrentDate = MAX(Tickets[Transaction Date])

VAR PriorDate = CALCULATE(MAX(Tickets[Transaction Date]), FILTER(ALLSELECTED(Tickets), Tickets[Transaction Date] <CurrentDate))

VAR MinDate = CALCULATE(MIN(Tickets[Transaction Date]), ALLSELECTED(Tickets))

VAR Difference = datediff(PriorDate,CurrentDate,day)

VAR Result = IF(CurrentDate = MinDate, 0, Difference)

RETURN Result
but this had a minor impact to optimization..

[Daniel29195 · Accepted solution]

hello @phorfanos 

can you please check this measure : 

link to download file from : 

https://drive.google.com/file/d/1FI-fqw3eB5vGo9lA1dgTzfRR-Vi3adCz/view?usp=sharing

summary : 
1.create dimdate

2.create dimagent

3.link both tables to Table4 (the table that contains the transactional data ) 

4. write the measure :   days since next trans - measure ( this calculates the average ) 

model  :

output : 

days since next trans - measure = 
 var current_date =  MAX(Table4[Transaction Date])
var c =  CALCULATE(COUNT(Table4[Transaction Date]) )

var datasource = 
CALCULATETABLE(
    SUMMARIZE(
        table4,
        dimagent[Agent],
        dimdate[Date]
    ),
        ALL(dimdate),
        dimdate[Date]  <=current_date
)

var datasource1 = 
CALCULATETABLE(
    SUMMARIZE(
        table4,
        dimagent[Agent],
        dimdate[Date]
    ),
        ALL(dimdate),
        dimdate[Date]  <=current_date
    )


var datasource2 = 
ADDCOLUMNS(
ADDCOLUMNS(
    datasource1,
    "prev_date", 
    SELECTCOLUMNS(
        offset(
            1, 
            ,ORDERBY(dimdate[Date], desc)
        ),
    "prev_date",  dimdate[Date]
    )
),
"diff", DATEDIFF([prev_date],  dimdate[Date],DAY) 
)

var res = SUMx(datasource2 , [diff]) / c

return if( current_date in VALUES(Table4[Transaction Date]), res)

let ne know if it works for you .

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅!

It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

[phorfanos]

Yes, definitely this measure is way much better than mine and produces results much faster! I just replaces the 2nd line "

var c =  CALCULATE(COUNT(Table4[Transaction Date]) )

to 

var c =  CALCULATE(DISTINCTCOUNT(Table4[Transaction Date]) )

But I will work on it a little bit more to optimize it further. Dear @Daniel29195 thnx a lot for your consideration and the time spent!

[Daniel29195 · Accepted solution]

@phorfanos 

use this code instead : 

(just some minor changes from the above code and removing some unecessary code ) .

days since next trans - measure = 
 var current_date =  MAX(Table4[Transaction Date])
var c =  CALCULATE(COUNT(Table4[Transaction Date]) )

var datasource1 = 
CALCULATETABLE(
    SUMMARIZE(
        table4,
        dimagent[Agent],
        dimdate[Date]
    ),
        ALL(dimdate),
        dimdate[Date]  <=current_date

    )

var datasource2 = 
ADDCOLUMNS(
ADDCOLUMNS(
    datasource1,
    "prev_date", 
    SELECTCOLUMNS(
        offset(
            1, 
            ,ORDERBY(dimdate[Date], desc)
        ),
    "prev_date",  dimdate[Date]
    )
),
"diff", DATEDIFF([prev_date],  dimdate[Date],DAY) 
)

var res = SUMx(datasource2 , [diff]) / c

return 
    res

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅!

It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

[Daniel29195]

@phorfanos  understood.

did you debug your code . 

by that i mean, did you try, to first measure in the table visual,  and check if you have performance issue.

then in measure 1, try to return the variables, and check on which variable the performance issue happen .

if you want, and possible for youm i dont have a problem going via zoom, to check it with you  via a call . 

best regards.