Create custom column with with effective dated field

[ShaelynFrench]

Hi,

I have a measure that is working just fine:

ProfType = 
var _max = MAXX( FILTER( ProfessionalType, ProfessionalType[EffDate] <= MAX( Dates[Date] ) ), ProfessionalType[EffDate] )
return
Calculate(
	max( ProfessionalType[ProfessionalType] ),
	Filter( ProfessionalType,
		ProfessionalType[EffDate] = _max
		)
	)

The problem is that it returns a result for every Professional, even if they have no other data for a time period. So I think it would be better if this measure was a calculated column in the table instead, but I can't figure out the syntax to make that happen. Help!

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your datamodel looks like, but please try the below for creating a new column whether it suits your requirement.

ProfType calculated column =
VAR _max =
    MAXX (
        FILTER (
            ProfessionalType,
            ProfessionalType[EffDate] <= EARLIER ( ProfessionalType[EffDate] )
                && ProfessionalType[ProfessionalType]
                    = EARLIER ( ProfessionalType[ProfessionalType] )
        ),
        ProfessionalType[EffDate]
    )
RETURN
    MAXX (
        FILTER (
            ProfessionalType,
            ProfessionalType[EffDate] = _max
                && ProfessionalType[ProfessionalType]
                    = EARLIER ( ProfessionalType[ProfessionalType] )
        ),
        ProfessionalType[ProfessionalType]
    )