March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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.
Solved! Go to 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.
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.
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.
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.
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.
@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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
90 | |
86 | |
83 | |
72 | |
49 |
User | Count |
---|---|
167 | |
149 | |
98 | |
73 | |
57 |