cancel
Showing results for
Did you mean:
Helper I

Current Month Percentage & Today's Percentage based on Headcount

Hi,

Thanks alot for the coommunity help I was able to make a measure to count below from dim table using below measures:

1. Today's date count = Calculate(Count(Fact_NOAV_data[Date]),Fact_NOAV_data[Date]=Today())

2. Current month date countCalculate(Count(Fact_NOAV_data[Date]),filter( Fact_NOAV_data ,eomonth(Fact_NOAV_data[Date],0)=eoMonth(Today(),0) ))

i have seperate table for calandar(DIM_Calander) as well which is linked to NOAV data(fact table).

Now i want to calculate below:

1.Today's Date % based on Today's Headcount

2. Current month % based on Average Headcount of current Month

Note: Headcount is stored in separate DIM Table (HC_DIM), NOAV means Not available.

Attached data Model for your information.

Data_Model

1 ACCEPTED SOLUTION
Community Support

Hi @Enan ,

Based on your requirements, you give your data model relationships and want to operate the following two measure requirements based on the two measures you solved earlier.

1.Today's Date % based on Today's Headcount

2. Current month % based on Average Headcount of current Month

Based on your current sample data (only one table), I created some test data and test measure, and then created the following two measures to try to implement your requirements.

``````Current month % based on Average Headcount of current Month =

var _Currentmonth= [Current month date count]

return

``````Today's Date % based on Today's Headcount =

var _TodaysDate= [Today's date count]

return

You can try it out first, and if necessary, upload sample data that contains all of your tables (especially the table Fact_NOAV_data you referenced in the previous measures) for us to further analyze and implement your needs.

Best Regards,

Neeko Tang

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

7 REPLIES 7
Community Support

Hi @Enan ,

Based on your requirements, you give your data model relationships and want to operate the following two measure requirements based on the two measures you solved earlier.

1.Today's Date % based on Today's Headcount

2. Current month % based on Average Headcount of current Month

Based on your current sample data (only one table), I created some test data and test measure, and then created the following two measures to try to implement your requirements.

``````Current month % based on Average Headcount of current Month =

var _Currentmonth= [Current month date count]

return

``````Today's Date % based on Today's Headcount =

var _TodaysDate= [Today's date count]

return

You can try it out first, and if necessary, upload sample data that contains all of your tables (especially the table Fact_NOAV_data you referenced in the previous measures) for us to further analyze and implement your needs.

Best Regards,

Neeko Tang

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

Helper I

Hi Neeko,

Can you please assit with the above measure of "Current month % based on Average Headcount of current Month"

the requirment is changed and now i need to calcualte the Average current month count instead of current month today's count.

so it will be
Current month % based on Average Headcount of current Month = Average count of current month / Average head count of current Month

You can refer to below post as well where i posted the question.

https://community.powerbi.com/t5/Desktop/Help-Need-Average-count-of-Month-along-with-criteria/m-p/30...

Helper I

Dear Neeko,

Thank you for your help and guidance.

Today's Date % based on Today's Headcount - Measure is working fine.

For Below Measure for Current Month % what you mean by [Current month date count], as i did understood it.

Also, if possble for you can you advise relevant study material, youtube, blog(Any one) where i can learn about the tenchique which used in these DAX measures. So i can alos learn and make my understanding.

Thank you

Community Support

Hi @Enan ,

The [Current month date count] in this metric [Current month % based on Average Headcount of current Month] refers to the measure you have created.

Regarding the monthly percentage, you can refer to the following documents to learn:

Measure showing current month and previous month p... - Microsoft Power BI Community

Measures - Month to Month Percent Change - (powerbi.tips)

If you want to learn DAX, please refer:

DAX in Power BI | A Complete Guide to Learn Power BI DAX (mindmajix.com)

Data Analysis Expressions (DAX) Reference - DAX | Microsoft Learn

Learn DAX basics in Power BI Desktop - Power BI | Microsoft Learn

Best Regards,

Neeko Tang

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

Helper I

Thank you Neeko for your guidance and help and sharing the the resource to learn about DAX.

Super User

@Enan , what is the structure of HC dim?

it would be like

Calculate(Count(HC_dim[Emp]),HC_dim[Date]=Today())

Or you can use today in place of the max date if you have a start and the end date

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Regular Visitor

Hi Amit, Thank you for you reply.

The HC_DIM Table have HC and date only.

for example.

Date: HC

01-Jan-22: 234

02-Jan-22: 230

03-Jan-22: 239

Announcements

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors