Dim Calendar Table Relationship with other Table

[_Melenios_]

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

[_Melenios_]

Hi @v-kelly-msft ,

Here you go :  https://easyupload.io/cjk7uu

Cheers!

[v-kelly-msft]

Hi  @_Melenios_ ,

So do you wanna count the total number of employees during the selected period?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[_Melenios_]

Hey @v-kelly-msft ,

Exactly. Picking up a random month and then being able to see where each employee is allocated(if applicable).

[v-kelly-msft]

Hi @_Melenios_ ,

Delete the relationship between the two tables then create a 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
CALCULATE(DISTINCTCOUNT('Table'[EMPLOYEE]),FILTER('Table',('Table'[START]<=_mindate&&_end>=_mindate)||('Table'[START]>=_mindate&&'Table'[START]<=_maxdate)))

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[_Melenios_]

Dear @v-kelly-msft ,

Thank you for your prompt reply and solution. Even if your solution finds the allocations in between departments correct, the problem here is that if i select any other month (out of the start-end range) it still doesn't shows/sums correct how many people i have on each department.

Eg.1 :

By selecting the earliest hired employee(12.06) i should be able to see him repeating from June until the end of 2021 on the same department.

Eg.2 : If i pick up August i should be able to see the summary and the allocation only for August and so on.

Thank you

[v-kelly-msft]

Hi  @_Melenios_ ,

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
CALCULATE(DISTINCTCOUNT('Table'[EMPLOYEE]),FILTER('Table',('Table'[START]<=_mindate&&_end>=_maxdate)))

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[_Melenios_]

Hey @v-kelly-msft ,

Thanks for the update.We're almost there i think. 🙂
The only problem i still have is that i want to visualise in my matrix not the start date from Employee Table but the months from the Calendar Table which i don't know how will happen since your measure did the trick but still when i'm selecting any month on the slicer i'm not able to reflect that on the matrix as well. 😞

[v-kelly-msft]

Hi  @_Melenios_,

Not quite get what you mean.

My measure is based on the Calendar date:

Measure = 
var _mindate=CALCULATE(MIN('DIM_CALENDAR'[Date]),ALLSELECTED(DIM_CALENDAR))
var _maxdate=CALCULATE(MAX('DIM_CALENDAR'[Date]),ALLSELECTED(DIM_CALENDAR))

So could you pls provide your expected output for me to better understand your logic?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[_Melenios_]

Hey @v-kelly-msft ,

So first thing is when you don't apply any filter your matrix is blank

And then what i really wanted is to apply the date from my DIM_Calendar to  the matrix and then being able to see each month my employees summary -allocations(if they're still working).

and desired output to be like below, correctly summing for each month selected(or all if no slicer is selected).

Thank you!

[v-kelly-msft · Accepted solution]

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:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[v-kelly-msft]

Hi  @_Melenios_ ,

Could you pls provide an expected output based on your sample data ?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!