Active members on a certain date

[DS_mho]

Hi, 

I just recently downloaded the desktop version and am currently "playing around" and trying things out, but have run into a problem. 

I have a long list of members of a user group where every member, among other dimensions, has a startdate and an enddate, for instance: 

Name                                Validtodatetime                            Validfromdatetime

Lars Sorensen                    03-05-2016                                       03-02-2015

I would like to count all the members active on 30-12-2015, 30-01-2016, 30-02-2016 and so on. I created a new coloumn and added the following formula to evaluate whether the member was active on the 30th of december 2015 (I would create a new colum to evaluate the other months): 

December = IF(AND(HRPPARTYPOSITIONTABLERELAT2226[VALIDFROMDATETIME]<=30-12-2015;HRPPARTYPOSITIONTABLERELAT2226[VALIDTODATETIME]>30-12-2015);1;0)

The formula should return a 1 for active members and a 0 for non active members which should be easy to aggregate. So far I have handled the data in Excel in the same way and with the same formula and it works just fine. However the formula in Power BI always returns 0. 

Is there something wrong with my formula (well, obviously there is but Power Bi doesn't return any errors) or am I simply approaching the whole thing in the wrong way ? 

If so, can anyone give me a hint as to how I should handle this ?

Best regards 

Martin 

[v-huizhn-msft]

Hi @DS_mho,

As the @Greg_Deckler's posted, you should use the DATE(Year,Month,Day) to verify the "30-12-2015" are data type. 

Best Regards,
Angelia

[Anonymous]

@DS_mho,

For a flexible solution, I suggest you consider adding a DimDate table (e.g. per https://blogs.msdn.microsoft.com/lukaszp/2015/03/05/power-bi-dynamic-date-filtering/)

Then you can use an "Active..." measure similar to the one discussed here (in Excel, but transferable to PBI) - https://www.powerpivotpro.com/2013/04/counting-active-rows-in-a-time-period-guest-post-from-chris-ca... 

[Greg_Deckler · Accepted solution]

December = IF(AND([VALIDFROMDATETIME]<=DATE(2015,12,30),[VALIDTODATETIME]>DATE(2015,12,30)),1,0)