Comparing (filtered) Month with Specific Month

[Anonymous]

Hey All :)!

First of all, small data sample:

Tablename: PolicyNew

YearMonth ||   PolicyNumber || Customer Number || Price (Policy)  || Product

202111              10                         1                               5                      Car

202111               11                        1                               6                      Car

202111               40                        2                               10                    House

202111                41                       3                               10                      Bike

Tablename: PolicyOld

YearMonth ||   PolicyNumber || Customer Number || Price (Policy)  || Product

202012              10                         1                               5                    Car

202012              40                        2                               10                    Car

202012               50                       4                               10                    House

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)

Basically i'm trying to figure out: 

- 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 

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.

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.

I've been thinking about using measures instead, but haven't come far.

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 > 202111'. However I haven't found a way to lookup withing the same table, based on Yearmonth value, either.

If you need anything from me, please let me know and I'll look into how I can provide such info.

Kind regards,

Daniël

[Aburar_123]

Hi Foxxon28,

Please find the below solution,

1. created a table as you have

2. then, created the below calculated columns,

Year = LEFT('Table'[YearMonth],4)

Month = RIGHT('Table'[YearMonth],2)

Previous_Year = 'Table'[Year]-1

PY_Price = LOOKUPVALUE('Table'[Price (Policy)],'Table'[Year],'Table'[Previous_Year],'Table'[Month],12,'Table'[PolicyNumber],'Table'[PolicyNumber])

So, i am getting the table as below,

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.

[Anonymous]

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

In my original example I would want to sum the cost of Policynumber 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?

[lbendlin]

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