Switch or if using var dax

[felipesaw]

Hi friends, i has several databases that change over time.

I has one measure :

Media nova =

var mediaanterior = calculate(AVERAGE(BasesHistoricas[ValorLinearizado]),ALL(BasesHistoricas[ContratoFantasia]),DATEADD(BasesHistoricas[Inicio mes competencia],-1,MONTH))

var mediaatual = calculate(AVERAGE(BasesHistoricas[ValorLinearizado]),ALL(BasesHistoricas[ContratoFantasia]))

var diferencamedias = mediaatual-mediaanterior

var mesesantes = CALCULATE(COUNT(BasesHistoricas[Range]),BasesHistoricas[Range]<BasesHistoricas[Final do mes])

var mesesfaltantes = CALCULATE(COUNT(BasesHistoricas[Range]),BasesHistoricas[Range]>=BasesHistoricas[Final do mes])

var formula = mediaatual+DIVIDE((diferencamedias*mesesantes),mesesfaltantes)

//----------------------------------------------------------------------------------------------

var valormesatual = SUM(BasesHistoricas[ValorLinearizado])

var tempomesatual = COUNT(BasesHistoricas[Range])

var valormesanterior = CALCULATE(SUM(BasesHistoricas[ValorLinearizado]),DATEADD(BasesHistoricas[Inicio mes competencia],-1,MONTH))

var tempoanterior = CALCULATE(COUNT(BasesHistoricas[Range]),DATEADD(BasesHistoricas[Inicio mes competencia],-1,MONTH))

var medianormalcalculo= DIVIDE(SUM(BasesHistoricas[ValorLinearizado]),COUNT(BasesHistoricas[Range]))

return

IF(

valormesanterior=BLANK(),

medianormalcalculo,

IF(

AND(

valormesanterior=valormesatual,tempoanterior=tempomesatual

),

calculate(formula,DATEADD(BasesHistoricas[Inicio mes competencia],-1,MONTH)),

formula

)

)

what i need is:

the first case make medianormalcalculo (i resolving this with valormesanterior=BLANK()),

after if valormesanterior=valormesatual,tempoanterior=tempomesatual, make medianormalcalculo again and again until  valormesanterior=valormesatual,tempoanterior=tempomesatual return false, after that i need formula, and if the next month valormesanterior=valormesatual,tempoanterior=tempomesatual return true, i need repeat the value of last month and not recalculated "formula", can anyone help me?

link with the model

https://wormhole.app/0xYvx#rIuJENgxaU_xcHUyccytKA

[v-yadongf-msft]

Hi @felipesaw ,

I'm a little confused about result your want.  In order to better understanding your demands and give the right solution, could you please tell me what's your expected output?

Thanks for your efforts & time in advance.

Best regards,

Yadong Fang

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

[felipesaw]

Hi friend, Output:

FILTER 01/03/2022

CONTRATO	MEDIA CONTRATO
1	1550

FILTER 01/04/2022

CONTRATO	MEDIA CONTRATO
1	1566,67

FILTER 01/05/2022 (as there was no change in value or months between the tables of 01/04/2022 and 01/05/2022 the value of their average should be equal)

CONTRATO	MEDIA CONTRATO
1	1566,67

database: https://wormhole.app/Jj05x#-UaWrGrHFtihCgcu4hMPXQ