Only show +5 year working anniversaries

[vanweelden]

Hi,

I have a table which shows me the working years for a certain year and month.

Now i want to filter this table by month so it shows me only the working anniversaries 5,10,15,20,25,30,35,40

But only the month when it occurs, not the following months.

Is this possible?

My Measures:

Sub Measure:

Dienstjaren1:=VAR SelectedDate =MAX(Datumtabel[Date])
VAR CurrentEmployee = MAX(Medewerkers[Datum in dienst basis])
VAR YearDiff = YEAR(SelectedDate) - YEAR(CurrentEmployee)
RETURN
IF ( MONTH(SelectedDate) > MONTH(CurrentEmployee);
YearDiff;
IF (MONTH(SelectedDate) = MONTH(CurrentEmployee) && DAY(SelectedDate) >= DAY(CurrentEmployee);
YearDiff;
YearDiff-1
)
)

End Measure:

Dienstjaren2:=VAR MaxDate=MAX(Datumtabel[Date])
VAR MaxYear=year(MaxDate)
Return
CALCULATE(AVERAGEX(Medewerkers;[Dienstjaren1]);
Medewerkers[Datum in dienst]<=MaxDate;
OR(Medewerkers[Datum uit dienst]>MaxDate;
ISBLANK(Medewerkers[Datum uit dienst]));
Medewerkers[Functie]<>"Commissaris")

[vanweelden]

@lbendlin Hi, so i've added a testfile. I'm looking for a way, that the table only shows me the month 1, where Employee09 reaches a 5 year anniversary and month 7 where Employee18 reaches a 20 year anniversary.

Is this possible? Thank you!

Test 

[lbendlin]

That test file seems to connect to a cube/data model?

[vanweelden]

Yes, my basis was a Power Pivot model. I moved this now to PBI.

Here is the PBIX file. Thank you in advance!

Test 

[lbendlin]

This is a bit trickier than it seemed because you have to treat month 1 separately.

bgcolor = 
var a = SELECTEDVALUE(Datumtabel[Month])
var b = CALCULATETABLE(summarize(Datumtabel,[Month],"cy",[Dienstjaren c]),REMOVEFILTERS(Datumtabel[Month]))
var c = minx(filter(b,[cy] in {5,20}),[Month])
var d = sumx(b,[cy])
return if(a=c,if(a>1,"lightgreen",if(d in {60,240},"lightgreen")))

[vanweelden]

You are the best!

Thank you very much!

Kind regards

Marcel

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...