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
gsrk1982
Frequent Visitor

Help required to filter prior record from history

Dear All,

 

I am working in Employee headcount analysis.Headcount will be calculated from employee history values.

 

I am having Employehistory and Date table.Dateofjoining active relationship and DateofLeaving Inactive relationship.Also I am having effctivedate column.In report I have Date slicer (Between) when slicer selected (Max date on slicer) I need to pass and filter prior values from history . Department and Grade are counted on relavant effective date.Kindly help me how to filter.T-SQL i have achived this.I am new to DAX so struggling to get....

 

 

 

Emp_IDEffective_FromDepartmentDesignationGradeJoin DateLeaving DateLeaving StatusRecord No
101-Jan-2016ProductSystem Engineer10001-Jan-2016NULL01
102-Jan-2017ProudctSr.System Engineer11001-Jan-2016NULL02
102-Mar-2018R&DAsst.Manager21001-Jan-2016NULL03
207-Jan-2015SupportSystem Engineer10007-Jan-2015NULL01
204-Dec-2015SupportSr.System Engineer11007-Jan-2015NULL02
210-Oct-2016ImplementationSr.System Engineer11007-Jan-2015NULL03
225-Mar-2017ImplementationSystem Analyst12007-Jan-2015NULL04
230-Nov-2017DeliverySystem Analyst12007-Jan-2015NULL05
201-Apr-2018DeliverySr.System Analyst13007-Jan-2015NULL06
218-Jul-2018DeliverySr.System Analyst13007-Jan-201518-Jul-201817
1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

Hi @gsrk1982 ,

 

I'm not so sure for your requirement, can you please explain more about these?

 

If You mean calculate available employee count in selected date range, you can try to use following measure formula:

Measure =
VAR _calendar =
    CALENDAR (
        MINX ( VALUES ( Date[Date] ), [Date] ),
        MAXX ( VALUES ( Date[Date] ), [Date] )
    )
RETURN
    CALCULATE (
        COUNTROWS ( VALUES ( Table[Emp_ID] ) ),
        FILTER ( ALLSELECTED ( Tabele ), [Effective_from] IN _calendar )
    )

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

1 REPLY 1
v-shex-msft
Community Support
Community Support

Hi @gsrk1982 ,

 

I'm not so sure for your requirement, can you please explain more about these?

 

If You mean calculate available employee count in selected date range, you can try to use following measure formula:

Measure =
VAR _calendar =
    CALENDAR (
        MINX ( VALUES ( Date[Date] ), [Date] ),
        MAXX ( VALUES ( Date[Date] ), [Date] )
    )
RETURN
    CALCULATE (
        COUNTROWS ( VALUES ( Table[Emp_ID] ) ),
        FILTER ( ALLSELECTED ( Tabele ), [Effective_from] IN _calendar )
    )

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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.