Need Help to Writing Dax for Last 12 month Headcount.

[adeeln]

Hello Experts,

I need to plot a trend line where I need to show the last 12-month employee headcount base on the selection (Relative slicer max date). I've HR fact table and date dimension joined based on employee date of joining. 

I've created DAX to calculate HC (Headcount) based on date selection(Max date from a relative slicer), it's working fine.

HC = 

VAR MaxDate =
    MAX ( 'DimDate'[Date] )
VAR EmpCnt =
    CALCULATE ( 
        COUNTROWS (
            CALCULATETABLE ( 'HR', 'HR'[DateOfJoining] <= MaxDate, ALL ( 'DimDate' ) )
        ),
        (
            ISBLANK ( 'HR'[TerminationDate] )
                || 'HR'[TerminationDate] > MaxDate
        )
    )
RETURN
    IF ( ISBLANK ( EmpCnt ), 0, EmpCnt )

In the same report, I need to show the same measure i.e. HC but for the last 12-months. For example, if I choose to show the last two calendar years OR two calendar Months OR two calendar Weeks OR two calendar Days OR Next calendar years OR this calendar years  from relative slicer then Dax should pick the max date from slicer and show the last 12 months from that max date.  

Here is the link PBIX file.

Link

[adeeln]

Need help to writing Dax for Last 12 month Headcount.Please help.

[v-shex-msft]

Hi @adeeln,

Can you please share some dummy data with raw table structure to help us clarify your data structure and test to coding formula?

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[adeeln]

@v-shex-msft My Requirment is that "Last 12 Month HC" shows On any selection from Relative Date Slicer.

[v-shex-msft · Accepted solution]

Hi @adeeln,

Here is a measure formula that calculates the last 12 month HC based on the current visual axis date field, you can try it if it meets your requirement:

HC Last 12 =
VAR currDate =
    MAX ( 'DimDate'[Date] )
RETURN
    CALCULATE (
        COUNTROWS ( 'HR' ),
        FILTER (
            ALL ( 'HR' ),
            'HR'[DateOfJoining] <= currDate
                && (
                    'HR'[TerminationDate] = BLANK ()
                        || 'HR'[TerminationDate] > currDate
                )
        )
    )

If this not help, share some dummy data or sample pbix file to test.

Regards,

Xiaoxin Sheng

[adeeln]

@v-shex-msft I have tried following measures this working fine On Last 2 Years Or 3 Years . But Not Working On Next Year,Last 2 Month, Last Weeks Or Last Days and Next Days,Month,Weeks.

HC Last 12 =

BE _Max1 =

MAXX ( allselected('DimDate'), 'DimDate'[Date] )

VAR _Max = date(year(_max1), month(_max1)-12, day(_max1))

VAR MaxDate =

MAX ( 'DimDate'[Date] )

VAR EmpCnt =

CALCULATE (

COUNTROWS (

CALCULATETABLE ( 'HR', 'HR'[DateOfJoining] <= MaxDate, ALL ( 'DimDate' ) )

),

(

ISBLANK ( 'HR'[TerminationDate] )

|| 'HR'[TerminationDate] > MaxDate

)

)

RETURN

IF ( ISBLANK ( EmpCnt ) , 0, if(max('DimDate'[Date])>=_Max, EmpCnt, blank() ))

[amitchandak]

@adeeln , for that you need independent table approch as shown in this video

https://www.youtube.com/watch?v=duMSovyosXE

[adeeln]

@amitchandak this solution is not working for Relative Date .