Forum Discussion
Filter multiple rows against another table using less than
Hi Anonymous ,
I have copy paste some lines on the contacts and changed the year this gave me the folllowing values per week:
The cumulative values are the ones until that week (excluding current week) this table was made only making the use of the contacts table now making use of the measure I have sent earlier:
Pre-Launch Contacts = VAR ProductYW = FIRSTNONBLANK('Product Activity'[YW],0) RETURN CALCULATE( SUM('CS Data'[Contacts Answered]), FILTER(ALL('CS Data'[YW]),'CS Data'[YW] < ProductYW))You can see that when you cross filter this with the YW of the product dates you get the results you need.
I had made a bit of an error in the sample files provided as I limited some of the data which was available.
In my real world file, I have weekly data which goes back to January 2019 (201901) and so the measure should be summing all contacts made in the weeks from 201901 until the product launch date (e.g. 202117).
As I have said before, the week comparisons work when I use a = but if try to find a sum of contacts before (<) or after (>) then i do not get any results.
Hi Anonymous ,
I have copy paste some lines on the contacts and changed the year this gave me the folllowing values per week:
The cumulative values are the ones until that week (excluding current week) this table was made only making the use of the contacts table now making use of the measure I have sent earlier:
Pre-Launch Contacts =
VAR ProductYW = FIRSTNONBLANK('Product Activity'[YW],0)
RETURN
CALCULATE(
SUM('CS Data'[Contacts Answered]),
FILTER(ALL('CS Data'[YW]),'CS Data'[YW] < ProductYW))
You can see that when you cross filter this with the YW of the product dates you get the results you need.
Thank you for all of your time and effort Miguel! Turns out I had the same measure as you have in your example solution and it turns out that I had a stray relationship connection which was not necessary but was messing up the measure showing the correct result (for some reason).
Thank you again!