Conditional aggregation / filter

Hi danakajoel ,

First create 4 columns in both tables:

Frequency = LOOKUPVALUE('Plan Lookup'[Column2],'Plan Lookup'[Column1],'Income Receipts'[Plan Number],Blank())

YEAR = YEAR('Income Receipts'[Period End Date])

Month = MONTH('Income Receipts'[Period End Date])

Quarter = QUARTER('Income Receipts'[Period End Date])

Then create a total column in both tables:

_Total = SWITCH('Income Receipts'[Frequency],
"Ad-Hoc",CALCULATE(SUM('Income Receipts'[Income Received])),
"Quarterly",CALCULATE(SUM('Income Receipts'[Income Received]),FILTER('Income Receipts','Income Receipts'[Year]=EARLIER('Income Receipts'[Year])&&'Income Receipts'[Quarter]=EARLIER('Income Receipts'[Quarter])&&'Income Receipts'[Plan Number]=EARLIER('Income Receipts'[Plan Number]))),
"Monthly",CALCULATE(SUM('Income Receipts'[Income Received]),FILTER('Income Receipts','Income Receipts'[YEAR]=EARLIER('Income Receipts'[YEAR])&&'Income Receipts'[Month]=EARLIER('Income Receipts'[Month])&&'Income Receipts'[Plan Number]=EARLIER('Income Receipts'[Plan Number]))),
"Annually",CALCULATE(SUM('Income Receipts'[Income Received]),FILTER('Income Receipts','Income Receipts'[YEAR]=EARLIER('Income Receipts'[YEAR])&&'Income Receipts'[Plan Number]=EARLIER('Income Receipts'[Plan Number]))),
"Bi-Annual",CALCULATE(SUM('Income Receipts'[Income Received]),FILTER('Income Receipts','Income Receipts'[YEAR]>=EARLIER('Income Receipts'[YEAR])&&'Income Receipts'[YEAR]<=EARLIER('Income Receipts'[YEAR])+1&&'Income Receipts'[Plan Number]=EARLIER('Income Receipts'[Plan Number]))))

And create a relationship using this column:

Finally in the filter pane,making the setting as below:

And you will see:

 For the related .pbix file,pls see attached.

Best Regards,
Kelly

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