Forum Discussion

jsadams's avatar
jsadams
Frequent Visitor
9 years ago

Filter Function Help

Hello,   I'm in the process of trying to pull before and after construction information. I'm struggling to connect my table of construction dates to the time intelligence functions. I believe using...
Need Help
Anonymous's avatar
Anonymous
Not applicable
9 years ago

I can imagine various ways of creating multiple relationships between the same tables, then using USERELATIONSHIP to "activate" the relationship you need... but I dunno, I can't decide if that is a good or bad idea.   Maybe somebody else has an opinion :)

 

 

Let's pretend both date columns in your construction table don't have any relationships.


Revenue - Year After Construction Ends := CALCULATE( SUM(Transactions[Revenue]), FILTER(ALL(Transactions[Date]), Transactions[Id]), Transactions[Date] > MAX(Construction[EndDate]) && Transactions[Date] <= MAX(Construction[EndDate]) + 365 ) )

 

Give me sum of revenue, but only for those transactions with a date starting at end of construction, and ending after 1 year... 

 

That ... works in a weird way when looking at multiple constructions... (uses the max ending date of any of the constructions), so you probably want to wrap that in a SUMX to iterate over all the constructions... one at a time, adding their revenue together.

 

Revenue - Year After Construction Ends := SUMX(Construction, CALCULATE( SUM(Transactions[Revenue]), FILTER(ALL(Transactions[Date]), Transactions[Id]), Transactions[Date] > MAX(Construction[EndDate]) && Transactions[Date] <= MAX(Construction[EndDate]) + 365 ) ) )

 

I could imagine that... doing something... :)

jsadams's avatar
jsadams
Frequent Visitor
9 years ago

Thanks for the help so far, but that didn't seem to work. Scrounging the forums/old questions I've asked. I thought the below formula would potentially be helpful, but it also didn't seem to give me the same number if I were to sumifs in excel.

 

Pre ATT = calculate(SUM(Master_table[visitors]),FILTER(Master_table,ABS((YEAR(Master_table[transaction date])*12+MONTH(Master_table[transaction date]))-YEAR(RELATED(Table1[construction date]))*12+MONTH(RELATED(Table1[construction date])))<=12))

 

Would applying the sumx around this potentially produce the solution I was looking for to make sure the calculation went through each row? (thanks for that tip btw).

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    HI jsadams,

     

    >>Would applying the sumx around this potentially produce the solution I was looking for to make sure the calculation went through each row? (thanks for that tip btw).

    Yes, sumx function will calculate through whole table which defined in the table expression.

    BTW, if the calculated expression not suitable for the defined table, it will return the static value.

     

    Regards,

    Xiaoxin Sheng