Often we need to derive first-month sales/donations. In the sample dashboard that I built using public FEC data, there are donors with multiple donations. The formula below allows determining donations in the first month since the first donation in a very efficient way.
First I will post the complete code and then review it in greater detail.
Contribution 1M Generate =
VAR Mo1Dates =
GENERATE (
VALUES ( ‘Main Donor Table'[FirstContributionDate] ),
DATESINPERIOD (
‘Contribution Details'[contribution_receipt_date],
‘Main Donor Table'[FirstContributionDate],
1,
MONTH
)
)
VAR Donations1Mo =
CALCULATE ( [Contributions], Mo1Dates )
RETURN
Donations1Mo
A calculated column in the main donor table already derived the first contribution date for each donor (See the dashboard for the code). The statement below uses a cross join (Generate) to derive all dates one month from the first contribution for each donor. VALUES ( ‘Main Donor Table'[FirstContributionDate] ) is the first table in the cross join, while the dates one month from it the second table. Once join the dates are prepared for future filtering.
VAR Mo1Dates =
GENERATE (
VALUES ( ‘Main Donor Table'[FirstContributionDate] ),
DATESINPERIOD (
‘Contribution Details'[contribution_receipt_date],
‘Main Donor Table'[FirstContributionDate],
1,
MONTH
)
)
The next step is to calculate all contributions for each donor for the set of generated dates. It also makes it much simpler and faster than using an additional iterator for each donor.
CALCULATE ( [Contributions], Mo1Dates )
RETURN
Donations1Mo
