Forum Discussion
Kudo Commanderportfolio count per time period
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!
8 Replies
- DataInsights
Super User
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 vResult3. Create visual using DatePeriod[DatePeriod]:
MarkPalmbergThank 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
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].