Dim Calendar Table Relationship with other Table

Dear all,

I have a Calendar table which i use as a reference for my dates-months slicers and i would like to create a relationship with an employee table to track down any changes/ upcoming moves that occured from one department to another in any given month.

The problem here is since not all the employees are changing departments every month if i corelate my Calendar.infodate with the Employee. Start Date i won't be able to see people that they haven't changed not a single department for any future month except from their initial one.

Any suggstions would be appeciated,

Cheers

Need Help

v-kelly-msft

5 years ago

Hi _Melenios_ ,

Sorry for the late reply.

Modify the measure as below:

Measure = 
var _mindate=CALCULATE(MIN('DIM_CALENDAR'[Date]),ALLSELECTED(DIM_CALENDAR))
var _maxdate=CALCULATE(MAX('DIM_CALENDAR'[Date]),ALLSELECTED(DIM_CALENDAR))
var maxdate=CALCULATE(MAX('DIM_CALENDAR'[Date]),ALL(DIM_CALENDAR))
var _end=IF(MAX('Table'[END])=BLANK(),maxdate,MAX('Table'[END]))
Return
IF(NOT(ISFILTERED(DIM_CALENDAR[Date])),DISTINCTCOUNT('Table'[EMPLOYEE]),CALCULATE(DISTINCTCOUNT('Table'[EMPLOYEE]),FILTER('Table',('Table'[START]<=_mindate&&_end>=_maxdate))))

And you will see: