topic Re: Lost Customers, "dynamic measures" in DAX Commands and Tips

Lost Customers, "dynamic measures"

joseluis1969240 — Fri, 22 Dec 2023 05:27:21 GMT

Good morning,
I'm trying to create two 'dynamic' measures in DAX that provide me with the number of lost customers and the amount of sales those customers had. In my case, lost customers are the ones who haven't had any sales in either the current fiscal year or the two previous fiscal years. The measure I'm using to calculate the sales of lost customers is as follows:

If I use this measure without applying any date filter (fiscal year), the measure works fine. However, if I apply a filter by fiscal year in the pivot table, the data appears blank. What I want to achieve is for the measure to be dynamic, meaning if I select fiscal year 2024 in the pivot table, the result should be the lost customers in fiscal year 2024. If I select fiscal year 2023, the results should be the lost customers in fiscal year 2023...
Can you please help me?
Thank you very much in advance,
Kind Regards,

Re: Lost Customers, "dynamic measures"

Dangar332 — Fri, 22 Dec 2023 09:15:37 GMT

Hi, joseluis1969240

Sales loss customer= Var yearmax= selectedvalue (calendario [fiscal_year]) Var result= sumx( All(ventas[customer Account]), Var sales3yrs = Calculate ( [sales], calendario [fiscal_year]<= yearmax, calendario [fiscal_year]>= yearmax-2) Var salesbefore3year = Calculate ([sales], calendario [fiscal_year]< yearmax-2) Return If(isblank(sales3yrs), salesbefore3year, sales3yrs) ) Return Result

Re: Lost Customers, "dynamic measures"

joseluis1969240 — Fri, 22 Dec 2023 12:02:05 GMT

Good morning:
Firstly, thank you for your response. I can't use the measurement you mentioned because the SELECTEDVALUE function doesn't exist in POWER PIVOT. Is it possible to modify the measurement using another function?
Thanks again

Re: Lost Customers, "dynamic measures"

Dangar332 — Fri, 22 Dec 2023 12:30:06 GMT

Hi, joseluis1969240

Try to replace selectedvalue with below

Var yearmax= max (calendario [fiscal_year])

Re: Lost Customers, "dynamic measures"

joseluis1969240 — Sat, 23 Dec 2023 09:11:46 GMT

Buenos días,
Gracias de nuevo por su respuesta. La solución que propones no funciona. A continuación, te mostraré los resultados proporcionados por la solución y cuáles son los resultados deseados:

Te agradecería mucho que siguieras ayudándome.
Gracias de antemano,

Re: Lost Customers, "dynamic measures"

Anonymous — Mon, 25 Dec 2023 02:15:44 GMT

Hi joseluis1969240 ,

I have created a simple sample, please refer to it to see if it helps you.

value_2 = var _1=SELECTEDVALUE('fiscal year'[year]) var _2yearsbefore=_1-3 return IF(_1=SELECTEDVALUE('Table'[year]),MAX('Table'[sales amount]),BLANK())

value_3= var _1=SELECTEDVALUE('fiscal year'[year]) var _2yearsbefore=_1-3 var _3=IF(_1=SELECTEDVALUE('Table'[year]),MAX('Table'[customer]),BLANK()) var _4= IF(_2yearsbefore=SELECTEDVALUE('Table'[year]),CALCULATE(SUM('Table'[sales amount]),FILTER(ALL('Table'),'Table'[year]=_2yearsbefore&&'Table'[customer]=SELECTEDVALUE('Table'[customer]))),BLANK() ) return _3

Measure 4 = MAXX(FILTER(ALL('Table'),'Table'[customer]=SELECTEDVALUE('Table'[customer])),[value_3])

Measure 5 = var _1=SELECTEDVALUE('fiscal year'[year]) var _2yearsbefore=_1-3 var _4= IF(_2yearsbefore=SELECTEDVALUE('Table'[year])&&[Measure 4]=BLANK(),CALCULATE(SUM('Table'[sales amount]),FILTER(ALL('Table'),'Table'[year]=_2yearsbefore&&'Table'[customer]=SELECTEDVALUE('Table'[customer]))),BLANK() ) return _4

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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

Re: Lost Customers, "dynamic measures"

joseluis1969240 — Thu, 28 Dec 2023 10:44:16 GMT

Good morning:
First of all, thank you for your response. I cannot test the solution because I am a Power Pivot user, and in Power Pivot, the SELECTEDVALUE function does not exist. Could you please tell me how I could adapt the DAX code to work with a different function than SELECTEDVALUE?
Thank you in advance