portfolio count per time period

[MarkPalmberg]

Sample data here. (<-- I hope that works!)

What I'm working toward is a count of householdlookupid per fundraiserlookupid and date period. Take, for example, fundraiserlookupid 0095050: Householdlookupid 0210855 was assigned to that fundraiser in date period 2016/5 - 2017/4 and stayed assigned until date period 2018/5 - 2019/4. So, for this particular combo of fundraiser and household, the desired output would be:

FundraserLookupid	DatePeriod	HouseholdCount
0095050	2016/5 - 2017/4	1
0095050	2017/5 - 2018/4	1
0095050	2018/5 - 2019/4	1

One of the things I'm trying to figure out is how to assign the dateperiod in the case of the middle row above, where the householdlookupid doesn't actually show up in the data but "is there" because it's "active" during that year.'

Very much appreciate any thoughts anyone has on the matter. Thanks!

[DataInsights]

@MarkPalmberg,

Try this solution.

1. Create a disconnected (no relationships) table DatePeriod:

2. Create measure:

HouseholdCount = 
VAR vYearEndDate =
    MAX ( DatePeriod[YearEndDate] )
VAR vResult =
    CALCULATE (
        COUNT ( 'sample table'[householdlookupid] ),
        vYearEndDate >= 'sample table'[FromDateEvaluationYearEndDate]
            && vYearEndDate <= 'sample table'[ToDateEvaluationYearEndDate]
    )
RETURN
    vResult

3. Create visual using DatePeriod[DatePeriod]:

[MarkPalmberg]

Thank you so much for taking the time to look at this, @DataInsights ! I got the measure built out.

I'm unclear how I get from Step 2 to Step 3 if DatePeriod table is unrelated to [sample table].

[DataInsights]

@MarkPalmberg,

The date logic is built into the measure, so no relationship is needed between the tables. The first two fields in the visual are from [sample table], the third field is DatePeriod[DatePeriod], and the fourth field is the measure [HouseholdCount].

[MarkPalmberg]

So if I create a table visual and add FUNDRAISERLOOKUPID and householdlookupid from [sample table], how do I add DatePeriod[DatePeriod] to that table?

[DataInsights · Accepted solution]

@MarkPalmberg,

Drag the field into the visual as shown below:

[MarkPalmberg]

[DataInsights]

@MarkPalmberg,

That's expected, since there is no relationship between the tables. However, when you add the measure HouseholdCount to the visual, the issue is resolved.

[MarkPalmberg]

Thank you, @DataInsights !