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
            )
        )
    )
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
        )
    )
)