Creating a summary table through power query without importing another source

[third_hicana]

Hi. Seeking help to DAX experts here. So, I wanted to make a table summarizing the table below. Here's my data

Here are the rules of how the table operates
1. Each calendar month, the table summarizes the total count of payroll contractor, and vacancy. 

2. Each calendar month should have 3 rows for total count of payroll contractor, and vacancy. Evertime a date is entered in "start date", it will add to the count depending on the contract type. And then, everytime a date is entered in the "End Date" ,it will subtract to the total contact of the contract type where the resource is under. 

3. As long as no date entered in the "End Date", the total count will just pick up the total count of previous calendar month of each contract type for the current month. So in short, it counts what is the remaining count of resource per contract type every calendar month. 

To illustrate, it should look like this :

Thank you in advance for your help. This is so advance and I'm not yet an expert in dax. Still learning. 🙂 

[BA_Pete · Accepted solution]

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:

[BA_Pete · Accepted solution]

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

[BA_Pete]

Hi @third_hicana ,

OK, so there's a few things to cover here:

1) How many rows does your source table have, and what would be the maximum rows it would ever contain?

2) Your count value may change between the start of the month and the end, so which count do you want to display? @start, @end, average of the two, or all of these?

3) I think you might be mixing up terms. You reference Power Query in the title of your post, but also reference DAX later on. Do you want this done in Power Query (M language), in the data model (DAX language), or is a combination of both ok?

4) Where do you want the ouput to go to? Your example screenshots imply that you want to output a new table into Excel, but do you actually want this in a Power BI data model/report?

Pete

[third_hicana]

Hi @BA_Pete  Here are my answers to your questions.

1. Currently, I have 48 rows excluding the header. Maximum is 80 rows estimate but  I don't know exactly how many workers will be added in the succeeding years. 

2. I want to display the count of  both per contract type. So basically, it adds and subtracts the total count per contract type. So, id the end date is filled out that means it will be subtracted to the remaning rows with no end date per contract type. Example:

if Jodi ends her contract on March 30,2022, therefore she will be excluded in the total count of employees under payroll next month (April). If the Vacancy is filled in with a contract type of payroll on March 2022, then it just replace Jodi. But if the vacancy is occupied on May, then it will add to the count of payroll on the month of May.  So it is more of addling and subtracting to the total count of employees per contract whenever there is a new hired or left the company. How many employees we hae per month after excluding those who left the company and those who ae newly added. The addition is dependent on start date and subtraction is dependent on end date. If there is no end date then, it will repeatedly count in the succeeding months. 

3. I am not yet familiar with the terms you mentioned. But I want to create that table in blank query using advance editor or whatever you may think wise thing to use. 
4. I want my output to be a new table in power Bi not in excel. I just showed in excel just to illustratehow it would look like in the data table in Power Bi. 

My goal is to have a count of each contract type per calendar month based on number of people who come and go in the organisation. 

Thank you for your time and effort to answer my post. 

[BA_Pete]

Hi @third_hicana ,

Cool, thanks. You've given me almost everything I need.

One more thing: Your source table vacancies have no Start/End dates. We can work without these, but that would mean that any time a vacancy row is added or removed, this change will be seen across every month, current and history. Is this acceptable and, if not, would it be feasible to manage the vacancies in the same way that you manage the other contracts i.e. with start and end dates for each? This would allow us to keep the temporal vacancies intact for history as they inevitably change moving forward.

Pete

[third_hicana]

Hi @BA_Pete, I need to also reflect in the bar graph the number of vacancies each month. I think what would work is to put a target start date so that it would not be a blank cell and operate the same as other contract type in terms of counting.  The latter i think woule be preferrable. Let me know if you need more info. 🙂

Thank you 🙂

[BA_Pete]

If you need to be able to track the correct number of vacancies each month, you'll need to manage them in the same way as the other contract types. For example:

Jim leaves and his last working day is 25/06/2021.

A new vacancy row is created with start date 26/06/2021 and no end date.

Jane fills this vacancy and her first working day is 18/07/2021.

You vacancy row is updated with an end date of 17/07/2021.

So your table now looks like this:

Resource	Start Date	End Date
Jim	01/01/1999	25/06/2021
Vacancy	26/06/2021	17/07/2021
Jane	18/07/2021

This would take a bit more management, but give you properly accurate results. Are you happy for me to assume this will be the process for my solution?

Pete

[third_hicana]

That would work. But my concern in that scenario is the vacancy row depends if the organisation plans to add a worker in the future or not. In your example, Jim's position does not automatically create a vacancy for the position that he left. So, it depends, if they will fill in the position Jim left in the future or they will not add a vacancy for Jim's replacement. In that case, maybe Jim's working duration is for a special project only and that is the ony time that they  need him. So, the number of vacancies was already planned. They may add vacancies or they may not. The vacancies is also dependent to the skill type that they need. So in short, the position left by Jim's does not automatically create a new vacancy. 

Thanks 🙂

[BA_Pete]

That's fine, I wasn't suggesting to make this happen automatically. Rather, that whoever manages the source table would need to update these vacancy dates appropriately.

Without doing something along these line, there's no way of getting a true vacancy figure each month, as there's no dates to relate the vacancies to.

I can put something together ignoring the vacancies and you can have a think about how you would like to manage them. At least you will have a template example of how I've managed the other contract types, so you can add them later if you choose to go down the dated-row path.

Pete

[third_hicana]

Hi Pete. Sure. Let's try that for now. Is it ok if I can go back to you in case your suggestion about managing the vacancy will be acceptable to the requirement? Thanks a lot

[BA_Pete · Accepted solution]

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:

[third_hicana]

Hi @BA_Pete I was able to replicate what you did above.  I hope I would be able to do this own my own in the future and pay it forward. Thank you very much for yor help 🙂

[third_hicana]

Hi @BA_Pete This is so cool. Will try this and get back to you 🙂 Thanks again. 

[third_hicana]

Hi @BA_Pete 
I transferred my data in SharePoint list and created the table again based on your tutorial. However, the table now show all months instead of reflectinng only until the current month. I also want to remove the blank row with zero value. Thanks

[BA_Pete · Accepted solution]

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

[third_hicana]

Thank you so much @BA_Pete . Got it now 🙂

[BA_Pete]

No problem, just waiting for all those kudos to roll in 😉

Pete

[third_hicana]

Gave you Kudos. You deserve it !😊

[BA_Pete]

Lol, thanks!

I was kind of half-joking, but I do appreciate it.

All the best 🙂

[third_hicana]

Hi @BA_Pete. There is change that I need to do in terms of calculating the numbers. So I removed the relative month in the filter because I want to see the next months' counts. However, if you can see in the picture, the payroll row does not count correctly. My goal is to calculate also in advance how many will left for the next 3 months. I tried to tweak and play the DAX of start of month and end of month but it gives me a wrong count. Appreciate your help. 

[BA_Pete · Accepted solution]

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

[third_hicana]

Thank you again @BA_Pete . It is now working. 🙂