Search code examples
powerbidaxpowerpivot

DAX Measure - Count Only Rows That Relevant This Month

I have in my Power BI data model table with the name "FactEarlyNotification" of employees that gave an advance notice for leaving the job. I need a measure that will count just the relevant emps for the last month of checking so I could predict how much employees we would have next month - Based on EndDate column. for example, today - I would like to see just those whose leaving in July only.

enter image description here

Solution

  • How do you want to determine the reference month? I assumed you wanted to compare against the current month but in theory you could replace the filter with a lot depending on your wish. I think the structure is what you are after?

    Leaving this month = 
    CALCULATE (
        DISTINCTCOUNT ( 'FactEarlyNotification'[EmpNum] ) ;
        FILTER ( 'FactEarlyNotification' ; MONTH ( 'FactEarlyNotification'[EndDate] ) = MONTH ( NOW() ) )
    )