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

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!

9 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

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