Reffering to [Value] of GENERATESERIES within Variables

[Anonymous]

I have a table that contains transactions done by users. Beside the user and the date, the transaction was created on, the table contains several other information like the users team lead, the project and process the transaction was made for, etc.

I now want to create a measure that calculates the average amount of transactions created within a selected periode by users with the same attribute (same project, same team lead, etc.). So the basic calculation would be (average transactions per day) / (average amount of users per day).

This is my approach:

av_Transactions_timeperiode = 

VAR start_date = IF(ISBLANK(MIN('Merged Data'[Created_Date])),0,MIN('Merged Data'[Created_Date]))
VAR end_date = IF(ISBLANK(MAX('Merged Data'[Created_Date])),1,MAX('Merged Data'[Created_Date]))

VAR __dates = GENERATESERIES(start_date,end_date)

VAR __trans_users = ADDCOLUMNS(__dates,
                                "Transactions",
                                CALCULATE(COUNT('Merged Data'[TransactionID]),'Merged Data'[Created_Date] = [Value]),
                                "Users",
                                CALCULATE(DISTINCTCOUNT('Merged Data'[User]),'Merged Data'[Created_Date] = [Value])
                            )

VAR av_transactions = AVERAGEX(__trans_users,[Transactions])
VAR av_users = AVERAGEX(__trans_users,[Users])

VAR av_trans_per_user = av_transactions/av_users

RETURN av_trans_per_user

However this doesn't work. Within the ADDCOLUMNS I can reffer to the [Value] of __dates but as soon as I want to reffer to it within the CALCULATE function it cannot be found. There is no difference when I try to use "__dates[Value]" instead, as __dates is a variable and not a "manifestated" table.

Is there any option I could reffer to the [Value] of __dates within the CALCULATE function? Or is there another approach to calculate the data I want?

If needed: The table "Merged Data" is sliced by a seperate date table that is related via the "Created_Date" column.

Thanks in advance

[tamerj1 · Accepted solution]

Hi @Anonymous 

please try

av_Transactions_timeperiode =
VAR start_date =
COALESCE ( MIN ( 'Merged Data'[Created_Date] ), 0 )
VAR end_date =
COALESCE ( MAX ( 'Merged Data'[Created_Date] ), 1 )
VAR __dates =
CALENDAR ( start_date, end_date )
VAR __trans_users =
GENERATE (
__dates,
VAR CurrentDate = [Date]
VAR Transactions =
CALCULATE (
COUNT ( 'Merged Data'[TransactionID] ),
'Merged Data'[Created_Date] = CurrentDate
)
VAR Users =
CALCULATE (
DISTINCTCOUNT ( 'Merged Data'[User] ),
'Merged Data'[Created_Date] = CurrentDate
)
RETURN
ROW ( "Transactions", Transactions, "Users", Users )
)
VAR av_transactions =
AVERAGEX ( __trans_users, [Transactions] )
VAR av_users =
AVERAGEX ( __trans_users, [Users] )
VAR av_trans_per_user = av_transactions / av_users
RETURN
av_trans_per_user

[Anonymous]

Thanks @tamerj1 ,

this works as intended.