Forum Discussion

felipesaw's avatar
felipesaw
Frequent Visitor
3 years ago

Switch or if using var dax

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

2 Replies

  • v-yadongf-msft's avatar
    v-yadongf-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    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's avatar
      felipesaw
      Frequent Visitor
      3 years ago

      Hi friend, Output:

       

      FILTER 01/03/2022

       

      CONTRATOMEDIA CONTRATO
      11550

       

      FILTER 01/04/2022

      CONTRATOMEDIA CONTRATO
      11566,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)

      CONTRATOMEDIA CONTRATO
      11566,67

       

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