How to create a matrix showing the value of each row at the End of Month?

[scorbin]

I am trying to create a matrix that shows the count of employees in each department at the end of every month. So I have something that looks like this:

This would be basic example with employee count showing the total employees in that department for that day. I have data for every day of the year. I also have a separate date table if that helps. 

I would like to create a matrix with the department being the rows, the date being the columns, and empolyee count at the end of the month for each department as the values. How might I go about doing this? 

[SteveHailey]

Hello @scorbin.

Since you already have a date table and data in your fact table for every day, you could:

Create a new End Of Month column, if you don't have this column already, in your date table:

End Of Month = EOMONTH( Dates[Date], 0 )

Create a new measure:

EOM Employee Count = 
CALCULATE (
    SUM ( Data[Employee Count] ),
    Data[Date] IN VALUES ( Dates[End Of Month] )
)

And use the new End Of Month column in your matrix columns, and your new measure in your matrix values:

I created a .pbix file here that you can download if it's helpful.

-Steve

[scorbin]

It looks like this half worked? I used the EoM column, but I think due to my actual data table being more complex and including multiple lines for the same date and count it would just sum the lines for that date. However, using just the regular count set to Max seemed to work. 

[SteveHailey · Accepted solution]

Ah, yes, if you have multiple lines for the same date, just change it to:

EOM Employee Count = 
CALCULATE (
    MAX ( Data[Employee Count] ),
    Data[Date] IN VALUES ( Dates[End Of Month] )
)

if you want the row with the highest employee count. 

Are you good? Do you need anything else on this one?

[scorbin]

Yes! It looks good now. Thanks!