Adding 1st 2nd 3rd and 3rdyearmore based on start date

[Easley08]

Hi,

is there a way to get the Anniv Year based on start date of this client 

to get the tagging of ( 1st year , 2nd year , 3rd year )

Thanks.

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your semantic model looks like but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

I hope the below can provide some ideas on how to create a solution for your semantic model.

INDEX function (DAX) - DAX | Microsoft Learn

Tag CC = 
VAR _startdate =
    MAXX (
        INDEX (
            1,
            SUMMARIZE ( Data, Data[Client], Data[Report_date] ),
            ORDERBY ( Data[Report_date], ASC ),
            ,
            PARTITIONBY ( Data[Client] )
        ),
        Data[Report_date]
    )
RETURN
    IF ( Data[Report_date] = _startdate, "New", "Existing" )

Anniversary year CC =
VAR _startdate =
    MAXX (
        INDEX (
            1,
            SUMMARIZE ( Data, Data[Client], Data[Report_date] ),
            ORDERBY ( Data[Report_date], ASC ),
            ,
            PARTITIONBY ( Data[Client] )
        ),
        Data[Report_date]
    )
VAR _currentrowdate = Data[Report_date]
VAR _diff =
    DATEDIFF ( _startdate, _currentrowdate, DAY ) / 365
RETURN
    IF ( Data[Report_date] = _startdate, 1, ROUNDUP ( _diff, 0 ) )

[Easley08]

Hi this is really helpful but i will try to implement this into SSAS Cube dax since im in live connection 

but the logic is in there this is amazing how you get this. my mind is exploding right on how you did this thanks a lot sir!