Forum Discussion

Zaidan30's avatar
Zaidan30
Frequent Visitor
1 year ago
Solved

Headcount on dashboard is not the same when i'm filtering a month and year.

Hello everyone,    I'm working on excel power query and i have measures of headcount, hires, terminations etc. I created a dashboard of all these KPIs but when i filter the dates that is, if i sele...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    1 year ago

    Zaidan30 Perfect! Thanks for the detailed information. I did it like the following. PBIX is attached below signature.

    Measure = 
    VAR __MaxDate = MAX( 'Dates'[Date] )
    VAR __Table = FILTER( ALL( 'Table' ), [Hire Date] <= __MaxDate )
    VAR __Table2 = FILTER( ALL( 'Table' ), [Termination Date] <= __MaxDate && [Termination Date] <> BLANK() )
    VAR __Result = COUNTROWS( __Table ) - COUNTROWS( __Table2 )
RETURN
    __Result
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
1 year ago

Zaidan30 Perfect! Thanks for the detailed information. I did it like the following. PBIX is attached below signature.

Measure = 
    VAR __MaxDate = MAX( 'Dates'[Date] )
    VAR __Table = FILTER( ALL( 'Table' ), [Hire Date] <= __MaxDate )
    VAR __Table2 = FILTER( ALL( 'Table' ), [Termination Date] <= __MaxDate && [Termination Date] <> BLANK() )
    VAR __Result = COUNTROWS( __Table ) - COUNTROWS( __Table2 )
RETURN
    __Result
Zaidan30's avatar
Zaidan30
Frequent Visitor
1 year ago

Hello Greg_Deckler ,

 

I'm so happy you solved my problem. You're really the best. i was working to solve this problem from almost 3 days before posting my request here. Thank you sooo sooo much for your help. 🙂 🙂