Comparing (filtered) Month with Specific Month

Question

Hey All :)!&nbsp;First of all, small data sample:Tablename: PolicyNewYearMonth ||&nbsp; &nbsp;PolicyNumber || Customer Number || Price (Policy)&nbsp; || Product202111&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Car202111&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;11&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;6&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Car202111&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;40&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; House202111&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 41&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Bike&nbsp;Tablename:&nbsp;PolicyOldYearMonth ||&nbsp; &nbsp;PolicyNumber || Customer Number || Price (Policy)&nbsp; || Product202012&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Car202012&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 40&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Car202012&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;50&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; House&nbsp;For a report im currently comparing this months results to december (previous year) results. This is done based on a 1-1 relationshup between the two tables on Key: PolicyNumber (Both tables are identical and the same source. Just pre-filtered on yearmonth)&nbsp;Basically i'm trying to figure out:&nbsp;- on Customer level: How much have we gained / lost compared to December (does policy numver exist in Table A, compared to B, if so, sum all Policy Prices on the same Customer number)- on Policy level: How much have we gained / lost compared to December (does a policy number exist in table A, compared to B - if so, sum it)- What type of products have we gained / lost&nbsp;&nbsp;By using a lot of calculated columns using the function RELATED() I've managed to succesfully get the right numbers.However, it's been requested to dynamically filter Yearmonth to compare last year's results. This sounded easy enough to me, but ended up difficult.&nbsp;If I were to load in all Yearmonth data, it would mean my 1-1 relationship would end up being a 1-many relationship. My calculated columns wouldn't know where to 'lookup' said values.&nbsp;I've been thinking about using measures instead, but haven't come far.&nbsp;Would you be so kind to help me out and share some tips, to get me started :)?Both tables are basically the same source, just filtered on Yearmonth, so technically I could get 1 table with all yearmonths from '202012 &gt; 202111'. However I haven't found a way to lookup withing the same table, based on Yearmonth value, either.&nbsp;If you need anything from me, please let me know and I'll look into how I can provide such info.&nbsp;Kind regards,Daniël

lbendlin · Answer

Put everything into the same table and then use DATEADD() to lookup the values for prior months and to compare them against "current"&nbsp; values as needed.&nbsp;

aburar_123 · Answer

Hi Foxxon28,&nbsp;Please find the below solution,&nbsp;1. created a table as you have&nbsp;2. then, created the below calculated columns,Year = LEFT('Table'[YearMonth],4) Month = RIGHT('Table'[YearMonth],2)Previous_Year = 'Table'[Year]-1PY_Price = LOOKUPVALUE('Table'[Price (Policy)],'Table'[Year],'Table'[Previous_Year],'Table'[Month],12,'Table'[PolicyNumber],'Table'[PolicyNumber])&nbsp;&nbsp;So, i am getting the table as below,&nbsp;3. Placed the visual as below where i have the slicers on the year and month,Please let me know whether it meets your expectation.&nbsp;&nbsp;

anonymous · Answer

Thanks!This has gotten me some places, but not the whole lot. Currently struggling to reverse search, cause of the 1-many relationship the table has, when you lookup Previous year&nbsp; and compare it with (filtered) month.For example. I want to check if a Policy from 202012 existed, that doesn't exist in (filtered) month (example 202010)&nbsp;Since that Policy number doesn't exist in the new month, I can't create a calculated column for it cause I have nothing to compare it with.&nbsp;In my original example I would want to sum the cost of Policynumber&nbsp;50, when I were to filter month 202010, as in that month, policy number 50 no longer exists.Do I need a measure to get this to work?

Forum Discussion

Comparing (filtered) Month with Specific Month

3 Replies

Related content

How to search specific word in table filter Calculations

Make measure ignore specific filter

Specific Filter

Filtering specific visuals

Specific matrix