Forum Discussion
Helper IITotal sum calculations based on year
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
2 Replies
- amitchandak
Super User
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/ba-p/1361529
Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retention/ba-p/1377458and
https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0
PBIDEV_10Hi 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.
