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 = CALCULAT...
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
            )
        )
    )
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

Hello Shravan133,

 

I don't want to replace my values with 0.