Help with getting the maxvalue for specific dimensions and dates

[lrozan]

Hello, this is what i need..I need the lastValue of the "Estado" Column...it will the max value of the column "Año Inscripcion"

I have the data at this dimension level

[Documento, Departamento, Nombre de Materia]

I want to get the "Estado" column

And i need only for the latest "Año Inscripcion"

So i did this, i created a calculated column:

Resultado Ultima Cursada =

  VAR Current_Date = TablaGeneral[Año Inscripción]

  VAR Previous_Date =

      CALCULATE (

          MAX ( TablaGeneral[Año Inscripción] ),

          TablaGeneral[Año Inscripción] <= Current_Date,

          ALLEXCEPT ( TablaGeneral,

        TablaGeneral[Documento],

        TablaGeneral[Nombre de materia],

        TablaGeneral[DEPARTAMENTO] )

      )

  RETURN

      CALCULATE (

          MAX ( TablaGeneral[Estado ] ),

          TablaGeneral[Año Inscripción] = Previous_Date,

          ALLEXCEPT (  TablaGeneral,

        TablaGeneral[Documento ],

        TablaGeneral[Nombre de materia                                                     ],

        TablaGeneral[DEPARTAMENTO] )

      )

Its working, but for some reason is duplicating the values!

The ones that I put in red shouldnt be there

Can you help me fix that DAX function?

Thanks

[Anonymous]

Hi  @lrozan ,

You can try the following dax;

Resultado Ultima Cursada =
  VAR Previous_Date =
      CALCULATE (
          MAX ( TablaGeneral[Año Inscripción] ),
          ALLEXCEPT ( TablaGeneral,
        TablaGeneral[Documento],
        TablaGeneral[Nombre de materia],
        TablaGeneral[DEPARTAMENTO] )
      )
  RETURN
      CALCULATE (
          MAX ( TablaGeneral[Estado] ),
          'TablaGeneral'[Año Inscripción]=Previous_Date,
          ALLEXCEPT (  TablaGeneral,
        TablaGeneral[Documento],
        TablaGeneral[Nombre de materia],
        TablaGeneral[DEPARTAMENTO] )
      )

If I have misunderstood your meaning, please provide your desired output and your pbix file without privacy information.

Best Regards,

Liu Yang

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