Creating a summary table through power query without importing another source

 

No problem. If you get stuck later on, just drop a note on this thread or me and I'll help you out.

 

1) First thing to do is create a filled end date column in Power Query. This will give our measures proper date bounds on every row to calculate between:

if [End Date] = null then Date.From(DateTime.LocalNow()) else [End Date]

 

2) Next, you'll need a calendar table. As a minimum, it will need a date column and a month column. I've attached an example PBIX to the bottom of this post so, if you don't have one already, there's a basic calendar table in there you can use.

 

3) Once these have both been created in PowerQuery, apply them to your data model. Check the model tab in Power BI Desktop once they've loaded to ensure the two tables are NOT related. PBI might do this automatically for you so, if it has, delete the relationship.

 

4) Create the following measures:

_startOfMonth = 
VAR __cDate = MIN('calendar'[date])
RETURN
CALCULATE(
    COUNTROWS(factResource),
    FILTER(
        factResource,
        factResource[Start Date] <= __cDate
        && factResource[End Date Filled] >= __cDate
    )
) + 0

 

_endOfMonth = 
VAR __cDate = MAX('calendar'[date])
RETURN
CALCULATE(
    COUNTROWS(factResource),
    FILTER(
        factResource,
        factResource[Start Date] <= __cDate
        && factResource[End Date Filled] >= __cDate
    )
) + 0

 

5) VISUALISE! Always use your calendar table to provide date dimensions to your visuals, and the measures will work just fine:

 

You'll notice that I've left vacancies in the data, but I've given them all relevant start/end dates as per our discussion.If you don't want to usethat process, you can just filter out the whole vacancy contract type in PQ and the remaining type will work fine.

 

Pete

PBIX attached down here somwhere:

Hi third_hicana ,

 

By default, Power BI will display all the data it has available in the data. In order to control the months that are visible in a visual when the data contains more than required, you'll need to filter the visual accordingly. My personal preference is to use a relative month column in my calendar table:

( Date.Year([date]) * 12 + Date.Month([date]) )
- ( Date.Year(Date.From(DateTime.LocalNow())) * 12 + Date.Month(Date.From(DateTime.LocalNow())) )

Change the data type of this new column to whole number (or decimal, if you're folding to an SQL server). You can then use this field in a visual-level filter as calendar[relativeMonth] <= 0 to only show data from current month or before.

 

The blank rows with zeroes can be fixed in two ways:

1) Filter your visual to only show the Contractor and Payroll contract types, or to not show blank contractor type, or

2) Remove the ' +0 '  from the end of each of the measures that I gave you. Note that doing this will stop rows appearing in your visual when you have no headcount of a particular contract type in any given month.

 

Pete

 

Hello again 🙂

My immediate guess is that the date bounds we gave the rows that have no end date is too limited for your new requirement. We currently have this, which ends a null duration with today's date:

 

if [End Date] = null then Date.From(DateTime.LocalNow()) else [End Date]

 

Try using this instead to be able to view three months into the future:

 

if [End Date] = null then Date.From(Date.StartOfMonth(Date.AddMonths(DateTime.LocalNow(), 4)))
else [End Date]

 

This will set a null end date to the first day of the fourth month from today. This gives three full months ahead.

 

Revert both of the measures back to their original code and this should work fine.

 

Pete