cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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 the "filter related" is the correct place to start, but had a few questions on how it functions and how I can make use of this combination.

 

I have 3 tables, one date key table, one construction info table that has [ID],[construction start],[construction end]. Also the transaction table with [id],[transaction date],[visitors],[revenue].

 

I have no issue creating the relationships between the tables, the problem is getting the formula to look for the construction start/end date and summing up visitors based on time intelligence functions. Understand that the construction info table has multiple ID's and multiple const/open dates.

 

 I believe I would filter my transactions table and relate that to the construction info table, but how would I include time intelligence functions to give multiple breakouts?

 

 

Best,

JA

7 REPLIES 7
Highlighted
Memorable Member
Memorable Member

Re: Filter Function Help

Might need a small pic of your model showing the relationships.  Are the construction/transaction tables related by ID, with construction id a unique value?

Highlighted
Frequent Visitor

Re: Filter Function Help

transaction table:

IDtransaction datetime segmentroom numbervisitorsrevenue
46521/1/2010Morning11$             7.50
78541/2/2010Afternoon25$         37.50
96351/3/2010Before Noon34$         30.00
78451/4/2010Morning46$         45.00

construction table

IDConstruction StartConstruction End
46521/1/20103/15/2010
78548/5/201112/7/2011
96359/7/2011

1/1/2012

Date key table

DateYearMonthWeekDay
1/1/20102010115
1/2/20102010116
1/3/20102010117

 

Tables are linked together by unique Building ID and I also linked everything to the Date table (in hopes of being able to manipulate views/cuts of data later).

Highlighted
Memorable Member
Memorable Member

Re: Filter Function Help

Ya, you will have an easy time w/ filtering transactions by date, and filtering transactions by construction... but you have an issue w/ your calendar table relating to construction table... since there are 2 dates.

 

If you make a relationship between your calendar table and construction on Construct Start... then filtering to June 2010 is going to show ONLY construction that STARTED in June, which is... maybe not what you want?

 

Let's talk about a specific measure to make this easier?  Like... you want... revenue year to date, broken out by construction?

Highlighted
Frequent Visitor

Re: Filter Function Help

I could make the construction 2 different tables. That would be an easy enough thing to do. What I'm trying to show is total revenue 12 months before construction starts, revenue 12 months after construction ends...and ideally any number of quarters, months etc. So the apples to apples comparison wouldn't be in individual months/years, but instead be based on number of days before or after construction started.

 

"1 year after renovations were completed, location 1 had x revenue, while location 2 had y revenue after it's remodel (that ended at a completely different time)."

 

"location 1 construction ended 1/1/2010, in 6 months it made $x. In the same 6 months before construction started location 1 made $y."....basically looking at the effectiveness of construction....

 

Thank you so much for the help!

 

 

Highlighted
Memorable Member
Memorable Member

Re: Filter Function Help

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

Highlighted
Frequent Visitor

Re: Filter Function Help

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

Highlighted
Community Support
Community Support

Re: Filter Function Help

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

Community Support Team _ Xiaoxin
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors