Measure and Date Filtered

[pelucapampa]

Hi All

I've table with workers, this table have StartWorkDate, and in some cases EndWorkDate.

Worker	StartDate(dd/mm/yy)	EndDate(dd/mm/yy)
Peter	01/01/2018
Jhon	20/05/2018
Sara	03/02/2018	10/05/2018
Mark	01/02/2018

I need a measure with COUNT the quantity of employees are ACTIVES into a Selected Month (Relative Date Filter).

I try thay

LegActivos = 
var SelectDateMin=FIRSTDATE('Date'[Date])
var SelectDateMax=LASTDATE('Date'[Date])
return CALCULATE(DISTINCTCOUNT(leg[worker]);leg[startdate]<=SelectDateMin;ISBLANK(leg[leg_fecegr]) && leg[leg_fecegr] >= SelectDateMax )

When I select April, i need to obtain 3 (Sara, Peter and Mark), On May, I obtain 4 .

[v-yulgu-msft · Accepted solution]

Hi @pelucapampa,

Please try this measure:

LegActivos =
VAR SelectDateMin =
    MIN ( 'Dim date'[Date] )
VAR SelectDateMax =
    MAX ( 'Dim date'[Date] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Table9[worker] ),
        FILTER (
            ALL ( Table9 ),
            Table9[StartDate(dd/mm/yy)] <= SelectDateMin
                && (
                    Table9[EndDate(dd/mm/yy)] = BLANK ()
                        || Table9[EndDate(dd/mm/yy)] >= SelectDateMax
                )
        )
    )

Best regards,

Yuliana Gu

[Greg_Deckler]

Take a look at these two Quick Measures as I think you want something like them.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365