Lost Customers, "dynamic measures"

[joseluis1969240]

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,

[Anonymous · Accepted solution]

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.

[joseluis1969240]

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

[Dangar332]

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 

[joseluis1969240]

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

[Dangar332]

Hi, @joseluis1969240 

Try to replace selectedvalue with below

Var yearmax= max (calendario [fiscal_year])

[joseluis1969240]

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,