topic portfolio count per time period in DAX Commands and Tips

portfolio count per time period

MarkPalmberg — Thu, 02 Sep 2021 02:52:08 GMT

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!

Re: portfolio count per time period

DataInsights — Fri, 03 Sep 2021 15:07:33 GMT

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]:

Re: portfolio count per time period

MarkPalmberg — Fri, 03 Sep 2021 19:14:45 GMT

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].

Re: portfolio count per time period

DataInsights — Fri, 03 Sep 2021 20:01:37 GMT

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].

Re: portfolio count per time period

MarkPalmberg — Fri, 03 Sep 2021 23:05:51 GMT

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

Re: portfolio count per time period

DataInsights — Sat, 04 Sep 2021 14:20:41 GMT

MarkPalmberg,

Drag the field into the visual as shown below:

Re: portfolio count per time period

MarkPalmberg — Tue, 07 Sep 2021 13:53:48 GMT

Re: portfolio count per time period

DataInsights — Tue, 07 Sep 2021 19:48:32 GMT

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.

Re: portfolio count per time period

MarkPalmberg — Wed, 08 Sep 2021 01:29:50 GMT

Thank you, DataInsights !