Complex query

[sabinocivita]

Hi all,

I have a table with 3 columns: numjob, openedjobdate, closedjobdate. I want to have a visual matrix that has months as rows and as values distinct count of job opened and distinct count of job closed for each month.

How to do it?

Thanks in advance 

[PC2790]

First of all you need to have a Calendar table with which your this table will have with.

Refer - Power BI Date or Calendar Table Best Method: DAX or Power Query? - RADACAD

Then have two relationship, one between Date from Calendar and openedjobdate(active)

The other between Date from Calendar and closedjobdate(inactive)

Then create two measures to count the number of jobs.

Please note for closeddate you will have to use USERELATIONSHIP dax

And at last in matrix, take month from Calendar table and the measures like this:

[sabinocivita]

Hi PC2790,

thanks for answer.

When you say "Then create two measures to count the number of jobs." on which columns are they based on? Cause I have only one column for numjob

[PC2790 · Accepted solution]

It can be like:

Count(Opendate) = Count(Table[numjob])

Count(Closedate) =  CALCULATE(count(Table[numjob]),USERELATIONSHIP(Table[closedjobdate],CalendarTable[Date]))

[sabinocivita]

Ok, very clear now, thanks a lot.