Current Month Percentage & Today's Percentage based on Headcount

[Enan]

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 count = Calculate(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

[v-tangjie-msft · Accepted solution]

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]

var _AverageTodaysHeadcount=AVERAGEX(FILTER(ALL(HC_DIM),'HC_DIM'[Date]=TODAY()),'HC_DIM'[HC])

return

DIVIDE(_Currentmonth,_AverageTodaysHeadcount)

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

var _TodaysDate= [Today's date count]

var _TodaysHeadcount=SUMX(FILTER(ALL(HC_DIM),'HC_DIM'[Date]=TODAY()),'HC_DIM'[HC])

return

DIVIDE(_TodaysDate,_TodaysHeadcount)

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. 

[Enan]

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...

Appreciate your help and guidance.

[Enan]

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

[v-tangjie-msft]

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)

Month over Month Calculation in Power BI using DAX - RADACAD

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.

[Enan]

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

[amitchandak]

@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...

[Enanb]

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