Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MarkPalmberg
Kudo Collector
Kudo Collector

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

 

FundraserLookupidDatePeriodHouseholdCount
00950502016/5 - 2017/41
00950502017/5 - 2018/41
00950502018/5 - 2019/41

 

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!

1 ACCEPTED SOLUTION

@MarkPalmberg,

 

Drag the field into the visual as shown below:

 

DataInsights_0-1630765211194.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

8 REPLIES 8
DataInsights
Super User
Super User

@MarkPalmberg,

 

Try this solution.

 

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

 

DataInsights_0-1630681429255.png

 

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

 

DataInsights_1-1630681509946.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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


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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

@MarkPalmberg,

 

Drag the field into the visual as shown below:

 

DataInsights_0-1630765211194.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




no_relationship.png

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thank you, @DataInsights !

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors