Switch or if using var dax

felipesaw — Tue, 22 Nov 2022 16:01:09 GMT

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

Re: Switch or if using var dax

v-yadongf-msft — Wed, 23 Nov 2022 08:27:50 GMT

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.

Re: Switch or if using var dax

felipesaw — Mon, 28 Nov 2022 14:05:44 GMT

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

topic Re: Switch or if using var dax in DAX Commands and Tips

Switch or if using var dax

Re: Switch or if using var dax

Re: Switch or if using var dax