Calculate measurment Dynamicly based on filters

[eli_p]

Hi, all I'm new to Dax,

I would like to use a calculation function to multiple column values with another column values of the next day to calculate "ARPU_D1"
I tried to use a filter for it.

The calculation that I want to translate to a Dax formula is:

"ARPU_D1" = "retention" * "Arpdau_all_col("install_date" + 1)" grouped by ("install_date", "app", "country".

For example for the first row in the table.
"retention" = 0.653846154 
Arpdau_all_col("install_date" + 1) = $0.0157

ARPU_D1 = 0.653846154 * $0.0157

This calculation should be applied for each row in the table.  

The rows that were used for the example is marked in blue and the column values from these rows are marked in red.

install_date	app	country	Arpdau_all_col	d0	d1	retention	ARPU_D0	ARPU_D1
15/10/2018 00:00	app1	Germany	$0.0172	26	17	0.653846154	$0.0172
15/10/2018 00:00	app1	United States	$0.0401	31	21	0.677419355	$0.0401
15/10/2018 00:00	app2	Germany	$0.0297	234	114	0.487179487	$0.0297
15/10/2018 00:00	app2	United States	$0.0600	2617	1450	0.554069545	$0.0600
16/10/2018 00:00	app1	Germany	$0.0157	21	8	0.380952381	$0.0157
16/10/2018 00:00	app1	United States	$0.0480	25	29	1.16	$0.0480
16/10/2018 00:00	app2	Germany	$0.0227	291	161	0.553264605	$0.0227
16/10/2018 00:00	app2	United States	$0.0489	2671	1931	0.722950206	$0.0489
17/10/2018 00:00	app1	Germany	$0.0208	25	10	0.4	$0.0208
17/10/2018 00:00	app1	United States	$0.0664	48	25	0.520833333	$0.0664
17/10/2018 00:00	app2	Germany	$0.0256	415	207	0.498795181	$0.0256
17/10/2018 00:00	app2	United States	$0.0569	4053	2364	0.583271651	$0.0569

How would I be able to do this?

Thanks

[eli_p]

Thanks for the reply. 

it seems that this expression won't work for me because the  'app' , 'country' , 'install_date' are not measurements.
 

I'm receiving this error.

The syntax for ''[app_id]'' is incorrect. (DAX(VAR App = LTV'[app_id]'VAR Country = 'LTV'[country]VAR CurrentDate = 'LTV'[install_date]RETURN 'LTV'[d1/d0] * CALCULATE ( MAX ( 'LTV'[Arpdau_all_col] ), FILTER ( 'LTV', 'LTV'[app] = App && 'LTV'[country] = Country && 'LTV'[install_date] = CurrentDate + 1 ) ))).

[Stachu]

there is typo in your syntax
LTV'[app_id]'

instead of

'LTV'[app_id]

[eli_p]

@Stachu 

Sorry, I did not mention this.

Still receiving an error.., 

A single value for column 'app_id' in table 'LTV' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

[Stachu]

the code I posted is for calculated column (to be added in LTV table), the error suggest that you use it as a measure - is that the case?

[eli_p]

@Stachu Sorry again, I changed it to column it worked thanks. 

@LivioLanzo the Data[retention] is not a column, it's a measurement so I tried to remove the calculation and I did not succeed with this option.

[eli_p]

@Stachu
So the solution worked for me to calculate the ARPU_D1
But when I'm trying to do the same for APRU_D2, ARPU_D3 etc.. which is basically uses the same formula just increases the date diff between  +2, +3 I'm receiving an error for circular dependency. 
"A circular dependency was detected: LTV[ARPU_D2], LTV[ARPU_D1_COL], LTV[ARPU_D2]."

I think it somehow related to the MAX ( 'LTV'[Arpdau_all_col])

Do you how to solve this issue? 

Thanks,

[Stachu]

I cannot replicate the issue, I can add new columns without a problem - I adjusted the syntax to match your naming convention, do these work properly?

Column = 
VAR App = 'LTV'[app_id]
VAR Country = 'LTV'[country]
VAR CurrentDate = 'LTV'[install_date]
RETURN
    'LTV'[d1/d0]
        * CALCULATE (
            MAX ( 'LTV'[Arpdau_all_col] ),
            FILTER (
                'LTV',
                'LTV'[app_id] = App
                    && 'LTV'[country] = Country
                    && 'LTV'[install_date] = CurrentDate + 1
            )
        )

and +2 days

Column 2 = 
VAR App = 'LTV'[app_id]
VAR Country = 'LTV'[country]
VAR CurrentDate = 'LTV'[install_date]
RETURN
    'LTV'[d1/d0]
        * CALCULATE (
            MAX ( 'LTV'[Arpdau_all_col] ),
            FILTER (
                'LTV',
                'LTV'[app_id] = App
                    && 'LTV'[country] = Country
                    && 'LTV'[install_date] = CurrentDate + 2
            )
        )

[eli_p]

Hi @StachuThis is "ARPU_D2"

ARPU_D2 = 
VAR v_App = 'LTV'[app_id]
VAR v_Country = 'LTV'[country]
VAR v_CurrentDate = 'LTV'[install_date]
RETURN
 'LTV'[ret_d2]
 * CALCULATE (
 MAX ( 'LTV'[Arpdau_all_col] ),
 FILTER (
 'LTV',
 'LTV'[app_id] = v_App
 && 'LTV'[country] = v_Country
 && 'LTV'[install_date] = v_CurrentDate + 2
 )
 )

This is "ARPU_D1"

ARPU_D1 = 
VAR v_App = 'LTV'[app]
VAR v_Country = 'LTV'[country]
VAR v_CurrentDate = 'LTV'[install_date]

RETURN
     'LTV'[ret_d1]
        * CALCULATE (
            MAX ( 'LTV'[Arpdau_all_col] ),
            FILTER (
                'LTV',
                'LTV'[app] = v_App
                    && 'LTV'[country] = v_Country
                    && 'LTV'[install_date] = v_CurrentDate + 1
            )
        )

As you see I'm using a different column for "retention" but it should not affect the circular dependency issue.
The dependency wis between : LTV[ARPU_D2], LTV[ARPU_D1], LTV[ARPU_D2].

All the variables are columns, only "retention" is a measurement ("ret_d1", "ret_d2") 

[Stachu]

ret_d1 and ret_d2 are measures? what's their syntax? 

if they're based on the LTV table then for sure they would cause the circular reference - they are used in columns, which are part of LTV, which is the table they are based on

[LivioLanzo]

@eli_p

wondering if you got a chance to test my solution?

[eli_p]

@LivioLanzo 
I'm trying this now, will update how it progressed 

[LivioLanzo]

Hi @eli_p

I built the below mode:

and then used this measure:

ARPU_D1 =
SUMX (
    SUMMARIZE (
        Data,
        'Calendar'[Date],
        Apps[app],
        Countries[country]
    ),
    CALCULATE (
        SUM ( Data[Arpdau_all_col] ),
        NEXTDAY ( 'Calendar'[Date] )
    ) * CALCULATE (
            SUM ( Data[retention] )
        )
)

[Stachu · Accepted solution]

try this code

Column =
VAR App = 'Table'[app]
VAR Country = 'Table'[country]
VAR CurrentDate = 'Table'[install_date]
RETURN
    'Table'[retention]
        * CALCULATE (
            MAX ( 'Table'[Arpdau_all_col] ),
            FILTER (
                'Table',
                'Table'[app] = App
                    && 'Table'[country] = Country
                    && 'Table'[install_date] = CurrentDate + 1
            )
        )