Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Enan
Helper I
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_ModelData_Model

 

 

1 ACCEPTED SOLUTION
v-tangjie-msft
Community Support
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]

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)

vtangjiemsft_0-1672650488970.png

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. 

View solution in original post

7 REPLIES 7
v-tangjie-msft
Community Support
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]

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)

vtangjiemsft_0-1672650488970.png

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. 

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.

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

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.

vtangjiemsft_0-1672710633016.png

 

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.

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

amitchandak
Super User
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...

 

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.