Forum Discussion

danakajoel's avatar
danakajoel
Frequent Visitor
5 years ago
Solved

Conditional aggregation / filter

Hi, newbie post here. I have a dataset of month end stock values over time, i.e. [Stock ID, Month End Date, Stock Value]   I also have a dataset of income received from these stocks over time, i.e....
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    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!

danakajoel's avatar
danakajoel
Frequent Visitor
5 years ago

Thank you amitchandak for taking the time to reply but I don't think that would give what I need?

v-kelly-msft's avatar
v-kelly-msft
Community Support
5 years ago

Hi  danakajoel ,

 

I created 2 sample tables as below:

Then create a measure as below:

Measure = 
var _lookupvalue=LOOKUPVALUE('Table 2'[Income Received],'Table 2'[Stock ID],MAX('Table'[Stock ID]),0)
Return
IF(MAX('Table'[Stock Value])>0&&_lookupvalue=BLANK(),1,0)

In the table visual,put measure in the filter pane ,select  measure is 1 .And you will see:

 

For the related .pbix file,pls see attached.

 

If above doesnt help solve your issue,remember to attach some sample data or .pbix file for test.

 

Best Regards,
Kelly

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

 

  • danakajoel's avatar
    danakajoel
    Frequent Visitor
    5 years ago

    Hi Kelly. I'm really grateful for you looking to help.  I can see the approach you are taking.  The issue is that stocks receive money monthly, quarterly, bi annually etc and so it's identifying where receipts are missing against an expectation.  I attach some randomised data to give a flavour of the challenge which I hope helps. For Stocks above, read Plans

     

    Struggling to figure out how to attach on here?!?!