Existing data vs corresponding previous data comparison

[amit_darak]

Hi,

I am currently working with insurance data where policy gets renewed or new policy is placed. I want to achieve something like this:-

There is a policy ABC-2021 which gets renewed and policy no changes to ABC-2022. This policy again gets renewed next year and becomes policy ABC-2023 and so on. I have data in below format.

My objective is whenever I select any policy no, I should see its previous policy performance. For eg if I select ABC-2023, I should see data for ABC-2022 as well like in the below chart.

I have written below DAX
last_year_value =

var prev_policy = MAX(Sheet1[Previous policy no])

var prev_sum =

SUMMARIZE(

FILTER(

SUMMARIZE(ALL(Sheet1), Sheet1[Policy no], Sheet1[Amount]),

Sheet1[Policy no] = prev_policy),

Sheet1[Amount]

)

return

SUMX(prev_sum,Sheet1[Amount])

But I get aggregated value on month column ie 204 which is sum of all 4 months whereas it should be - Apr-35, Feb-65, Jan-12, Mar-91

What am I missing?

Regards,

Amit Darak

[lbendlin · Accepted solution]

It's much simpler.

[amit_darak]

Hi,

It worked well when I didn't use the filter function.

I tried using this dax with filter function, it gave me blank values.

last_year_value =

var prev_policy = MAX(Sheet1[Previous policy no])

var ly_value = CALCULATE(SUM(Sheet1[Amount]),FILTER(Sheet1, Sheet1[Policy no] = prev_policy))      

return

ly_value

Any idea why it gives blank values. As I have to apply multiple filters in my formula, this isn't working in my original work.

Regards,

Amit 

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[amit_darak]

Hi,

I think I complicated it unnecessarily. It was pretty simple.

Thanks.