Forum Discussion

Sam_BI_Analyst's avatar
Sam_BI_Analyst
Frequent Visitor
2 years ago
Solved

Need Help with Dax

Hello Community,

 

I need some help with correcting my dax formula. My ending_hc is working as expected but my starting_hc isn't. Please find below table with the details.

 

Ending_HC =
CALCULATE(
COUNTROWS('Headcount Report'),FILTER('Headcount Report',
'Headcount Report'[YEAR ] = SELECTEDVALUE('Headcount Report'[YEAR ]) &&
'Headcount Report'[MONTH#] = SELECTEDVALUE('Headcount Report'[MONTH#])
))

 

Starting HC =
VAR _CurrentYear = SELECTEDVALUE('Headcount Report'[YEAR ])
VAR _CurrentMonth = SELECTEDVALUE('Headcount Report'[MONTH#])
VAR _PreviousMonth = IF(_CurrentMonth = 1, 12, _CurrentMonth-1)
VAR _PreviousYear = IF(_CurrentMonth = 1, _CurrentYear-1, _CurrentYear)
var edhc = CALCULATE(
COUNTROWS('Headcount Report'),FILTER('Headcount Report',
'Headcount Report'[YEAR ] = _PreviousYear &&
'Headcount Report'[MONTH#] = _PreviousMonth
))
RETURN
IF(_CurrentYear=2021 && _CurrentMonth=2,[Starting_HC],edhc)

My output - 

 

 

Ritaf1983 

Warm Regards

Need Help
  • Sam_BI_Analyst's avatar
    Sam_BI_Analyst
    2 years ago

    I found the answer,

     

    Starting_HC =
    VAR CurrentYear = SELECTEDVALUE('Headcount Report'[YEAR ])
    VAR CurrentMonth = SELECTEDVALUE('Headcount Report'[MONTH#])
    VAR PrevYear = SELECTEDVALUE('Headcount Report'[prevYear])
    VAR PrevMonth = SELECTEDVALUE('Headcount Report'[PreviousMonth])

    RETURN
    IF (
        CurrentYear = 2021 && CurrentMonth = 2,
        [Ending_HC],
        CALCULATE(
            COUNTROWS('Headcount Report'),
            FILTER(
                ALL('Headcount Report'),
                'Headcount Report'[YEAR ] = PrevYear &&
                'Headcount Report'[MONTH#] = PrevMonth
            )
        )
    )

3 Replies

  • Shravan133's avatar
    Shravan133
    Super User
    2 years ago

    try this refined measure and apply your logic to it:

    Starting_HC =

    VAR _CurrentYear = SELECTEDVALUE('Headcount Report'[YEAR ])

    VAR _CurrentMonth = SELECTEDVALUE('Headcount Report'[MONTH#])

    VAR _PreviousMonth = IF(_CurrentMonth = 1, 12, _CurrentMonth - 1)

    VAR _PreviousYear = IF(_CurrentMonth = 1, _CurrentYear - 1, _CurrentYear)

    VAR _StartingHC =

        CALCULATE(

            COUNTROWS('Headcount Report'),

            FILTER(

                'Headcount Report',

                'Headcount Report'[YEAR ] = _PreviousYear &&

                'Headcount Report'[MONTH#] = _PreviousMonth

            )

        )

    RETURN

    IF(

        ISBLANK(_StartingHC),

        0,

        _StartingHC

    )

  • Sam_BI_Analyst's avatar
    Sam_BI_Analyst
    Frequent Visitor
    2 years ago

    I found the answer,

     

    Starting_HC =
    VAR CurrentYear = SELECTEDVALUE('Headcount Report'[YEAR ])
    VAR CurrentMonth = SELECTEDVALUE('Headcount Report'[MONTH#])
    VAR PrevYear = SELECTEDVALUE('Headcount Report'[prevYear])
    VAR PrevMonth = SELECTEDVALUE('Headcount Report'[PreviousMonth])

    RETURN
    IF (
        CurrentYear = 2021 && CurrentMonth = 2,
        [Ending_HC],
        CALCULATE(
            COUNTROWS('Headcount Report'),
            FILTER(
                ALL('Headcount Report'),
                'Headcount Report'[YEAR ] = PrevYear &&
                'Headcount Report'[MONTH#] = PrevMonth
            )
        )
    )