Forum Discussion

rinaemo's avatar
rinaemo
Frequent Visitor
3 years ago
Solved

Count of Rows using Master Calendar

Hi Experts,

 

I was told to create a master calendar to count the number of employees for the start and end date by Month and Year. I am very new to PowerBI and i have no idea how to start with and how to proceed with the data.

 

Over here i have 4 columns in my current table:

1. Employee ID

2. Employee Name

3. Join Date

4. End Date

 

I need to create a visualization of Month and Year of how many employees is present in the company...

 

Can anyone advise on how do i create this? Much appreciated! Thanks!

Need Help
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    Hi , rinaemo 

    Thanks for yoour quick response ! 

    According to your description, you just want to calculate the number of people who are on the job in the middle of the year and month, and for your needs, I have implemented it in the above example, and it is according to your logic to calculate.

     

    If you want to filter based on the year and month you choose, you just need to put [Date] in 'Calendar' in slicer, and then you can implement your needs,like this:

     

    If this method does not meet your needs, you can provide us with  the desired output sample screenshot, so that we can better help you solve the problem.

    Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

     

    Best Regards,

    Aniya Zhang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

7 Replies

  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    Community Support
    3 years ago

    Hi , rinaemo 

    According to your description, you want to "create a visualization of Month and Year of how many employees is present in the company.". Right?

    Here are the steps you can refer to :
    (1)This is my test data:

    (2)We can create a master date you need , we can click "New Table" and enter this:

    Calendar = ADDCOLUMNS( 
CALENDAR(FIRSTDATE('Table'[Join Date]),TODAY()),
"year", YEAR ( [Date] ),
"month", MONTH([Date])
)

    And we do not need to make relationship between two tables.

     

    (3)In the fact table , we need to click "New Column" to create a calculated column to show the end [End Date] if the [End Date] is BLANK() then we retun the TODAY() date:

    End Date result = IF([End Date] = BLANK() , TODAY() , [End Date])

     

    (4)Then we can create a measure like this:

    Measure = var _cur_year_month = MAX('Calendar'[year_month])
var _t = FILTER( 'Table' , _cur_year_month >= YEAR('Table'[Join Date])*100+MONTH('Table'[Join Date])  && _cur_year_month<=YEAR('Table'[End Date result])*100+MONTH('Table'[End Date result]))
return
COUNTROWS(_t)

    (5)Then we put the fields we need on the visual and we can meet your need:

    Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

     

    Best Regards,

    Aniya Zhang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

    • rinaemo's avatar
      rinaemo
      Frequent Visitor
      3 years ago

      Hi Aniya,

       

      My end date is from the initial table, how do i do the following to link to my other table? 

       

      End Date result = IF([End Date] = BLANK() , TODAY() , [End Date])

       

      On top of that, the requirement just changed to only calculating dates that are of 1st of the month and year. I need to take in consideration of the start date and end date on every 1st of the month to keep track of the number of employees in the company. 

      • v-yueyunzh-msft's avatar
        v-yueyunzh-msft
        Community Support
        3 years ago

        Hi, rinaemo 

        Hello, if your end date is from another table, then you can establish a relationship for the two tables, and then get the corresponding end date.

        For your following needs, if you can, I hope you can provide us with sample data of all your tables, as well as your relationship, and give us the final result you want in the form of a table according to the sample data, so that we can better help you.

         

        Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

         

        Best Regards,

        Aniya Zhang

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly