Conditional aggregation / filter

[danakajoel]

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. [Stock ID, Month End Date, Income Received]

The model is built with date table and various other look up tables.

It's a large dataset but it appears that in some instances, we've not reconciled the income in our system so we have stock values but no associated income.

I'm trying to create a filter which shows me where we have postive stock values but haven't reconciled any income in the past 6 months. I'm sure it's straightforward but I'm drawing a blank 😞

TIA

[amitchandak]

@danakajoel , based on what I got. You can rolling like this example

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

[danakajoel]

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

[v-kelly-msft]

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]

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?!?!

[v-kelly-msft]

Hi @danakajoel ，

You could upload your sample data to a Cloud service then share a public to us.

Best Regards,
Kelly

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

[danakajoel]

Thank you Kelly. Here's a randomised data set. Many thanks

https://1drv.ms/x/s!Ah__lQK3C3gfhvcQBqYAjlw0Foakvg?e=bSdPMi

[v-kelly-msft · Accepted solution]

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]

Hi Kelly, sorry, I've been working off site for the last couple of weeks. I'll look to update my model next week and let you know how I get on 😀

[v-kelly-msft]

Hi  @danakajoel ,

It's OK,waiting for your feedback.

Best Regards,
Kelly

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