Total sum calculations based on year

[PBIDEV_10]

Hi

I am working on a member dataset. Where the data is at date level. Data looks like:

MemberNo.	Active flag	Date	FeePaidDate	Difference in years	Price per year
1	1	1/1/2023	12/31/2022	-1	300
1	1	1/2/2023	12/31/2022	-1	300
1	1	1/3/2023	12/31/2022	-1	300
1	1	1/4/2023	12/31/2022	-1	300
1	1	1/5/2023	12/31/2022	-1	300
1	1	1/6/2023	12/31/2022	-1	300
1	1	1/7/2023	12/31/2022	-1	300
1	1	1/8/2023	12/31/2022	-1	300
2	1	1/1/2023	12/31/2022	-1	300
2	1	1/2/2023	12/31/2022	-1	300
2	1	1/3/2023	12/31/2022	-1	300
2	1	1/4/2023	12/31/2022	-1	300
2	1	1/5/2023	12/31/2022	-1	300
2	1	1/6/2023	12/31/2022	-1	300
2	1	1/7/2023	12/31/2022	-1	300
2	1	1/8/2023	12/31/2022	-1	300
10	1	1/1/2023	12/31/2021	-2	300
10	1	1/2/2023	12/31/2021	-2	300
10	1	1/3/2023	12/31/2021	-2	300
10	1	1/4/2023	12/31/2021	-2	300
10	1	1/5/2023	12/31/2021	-2	300
10	1	1/6/2023	12/31/2021	-2	300
10	1	1/7/2023	12/31/2021	-2	300
10	1	1/8/2023	12/31/2021	-2	300
11	0	1/1/2023	12/31/2020	-3	300
11	0	1/2/2023	12/31/2020	-3	300
11	0	1/3/2023	12/31/2020	-3	300
11	0	1/4/2023	12/31/2020	-3	300
11	0	1/5/2023	12/31/2020	-3	300
11	0	1/6/2023	12/31/2020	-3	300
11	0	1/7/2023	12/31/2020	-3	300
11	0	1/8/2023	12/31/2020	-3	300

Now I want to generate following matrices: 

Difference Year	No. of Inactive members	Total sum to be paid
This year (0)
Last year (-1)
Last to last year (-2)
More than 2 years (-3)	1	900

Difference Year	No. of Active members	Total sum to be paid
This year (0)
Last year (-1)	2	600
Last to last year (-2)	1	300
More than 2 years (-3)

How can I achieve that with DAX? I tried to implement like this but gives wrong result:

Total sum to be paid = 
VAR DifferenceYear = CALCULATE(SUM(Fact_table[DifferenceYear]), LASTDATE(Date[Date]))
VAR PricePerYear = CALCULATE(SUM(Fact_table[PricePerYear]), LASTDATE(Date[Date]))
RETURN DifferenceYear * PricePerYear 

Regards

PGP

[amitchandak]

@PBIDEV_10 ,

Based on what I got , a new measure 

sumx(Values(Fact_table[MemberNo]), [Total sum to be paid])

Or a combination these two

Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/b...
Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retenti...

and

https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0

https://amitchandak.medium.com/power-bi-get-the-sum-of-the-last-latest-value-of-a-category-f1c839ee8...

[PBIDEV_10]

Hi @amitchandak . Thank you for your reply. But I didnot understand the solution you gave. I need to pick up the last row for each member from the fact table and use price per year and difference in year. But when I use SUM in the variable, it gives me vague results.