Time Intelligence Calculations

Rsanjuan There are two ways to do this. One way is to use a bunch of CALCULATE and FILTER conditions and a disconnected date table, but this means you cannot use any standard time intelligence functions and you have to fake them yourself. This gets complicated quickly and is hard to debug because you end up with measures that are 20 lines of code. It's frequently what I use but I don't like it, so I'm going to recommend a newer way I've found: the easier way is to transform your data so that it is represented as if it were transactional rather than durational. This was the solution I was looking for in a similar situation to yours a while back, and OwenAuger came up with the formula to make it work.

If you have a table with rows that each have only one significant date, you can hook it up to a date table and use standard time intelligence functions. So to do this you will create a second table. In it, rather than having a single row for each job with a start and end date, each job would have multiple rows, one row for each date during its active period between start and end. Then you can link that new table up to a date table and do standard time intelligence calculations using a distinctcount of either unique job ID or unique person ID, depending on which of those is appropriate for your scenario.

Click on the Modeling tab in Power BI and hit New Table. We'll call it WorkSchedule. I'm going to have to assume that the table in your earlier screenshot has some unique job ID or employee ID column. I'm going with employee ID and further assuming that your screenshotted table is named JobTable.

WorkSchedule = 
SUMMARIZE (
    GENERATE (
        JobTable,
        CALCULATETABLE (
            VALUES ( DateTable[Date] ),
            DATESBETWEEN ( DateTable[Date], JobTable[NewJob.Job Start Date], JobTable[NewJob.Job End Date] )
        )
    ),
    DateTable[Date],
    JobTable[EmployeeID]
)

Now you have a table that looks like a normal transactional fact table. Like retail sales for instance, where each sale at the register has a row, and that row has a single date that it happened on. That's the structure expected for time intelligence, not the start date / end date structure you have. In this case each day that an employee worked has a single date. If you connect that date column to a date table, you can write formulas like

WorkingCount = DISTINCTCOUNT(WorkSchedule[EmployeeID])

Working Month to Date = TOTALMTD( [WorkingCount], DateTable[Date])

Make sense? If you haven't already, check out the other thread I linked to above. It gives some examples of a dataset and how it's all meant to behave.