Grandfather asks for a report on the birthday gift plan

[gennaro_19]

Hello Community,
Thank you in advance for your attention.
A grandfather asks a bank to pay a sum of money for each of his grandchildren's birthdays.

He asks the bank for a report on the birthday gift plan.

The grandfather's request is summarised in the table below.

Name	Birth	Amount / year	Duration
Marco	15/04/2022	20	5
Paolo	18/09/2023	10	5
Luca	20/06/2024	40	5

The bank with a DAX measure would like to achieve this result:

Name	2023	2024	2025	2026	2027	2028	2029	Totali
Marco	20	20	20	20	20			100
Paolo		10	10	10	10	10		50
Luca			40	40	40	40	40	200
Totali	20	30	70	70	70	50	40	350

How to get the desired result from the bank??
Thks

[tamerj1 · Accepted solution]

@gennaro_19 
Yes you are right. I've got a couple functions wrong.

Unfortunately, I was not able to download the file waiting for your approval to my download request.
Not sure why Years[Year] is marked as a wrong syntax. Have you already created the Years table?
Meanwhile you may try

Annual Installment =
SUMX (
    GENERATE (
        'Regali',
        INTERSECT (
            DISTINCT ( Years[Year] ),
            GENERATESERIES (
                YEAR ( 'Regali'[Birth] ),
                YEAR ( 'Regali'[Birth] ) + 'Regali'[Duration] - 1,
                1
            )
        )
    ),
    'Regali'[Amount]
)

*Update

I have been able to download the file. The forumla works fine, I just had to rename the Year table as Years. Attached the sample file with the solution

[tamerj1]

Hi @gennaro_19 

Such a wise grandfather!

Hope I can help him with the following proposal 

Annual Installment =
SUMX (
CROSSJOIN (
'Table',
EXCEPT (
DISTINCT ( Years[Year] ),
GENERATESERIES (
YEAR ( 'Table'[Birth] ),
YEAR ( 'Table'[Birth] ) + 'Table'[Duration] - 1,
1
)
)
),
'Table'[Amount/Year]
)

[gennaro_19]

Hi Tamerij1, 😀
First of all Thank you for taking the time 🙏
I tried to write the measure, but from the DISTINCT function onwards, it goes into error as you can see from the attached image.

and I couldn't figure out why

A few clarifications:
1) the fact table is called ‘Gifts’;
2) the dates table is called ‘Calendar’;
3) ‘Gifts[Amount] is the annual amount, which is paid every year for 5 years.

I hope you can help me again.
🙏
Thank you
g

[tamerj1]

@gennaro_19 

Missing the closing Apostrophe in the table name

 The calendar table has to be disconnected or otherwise create a Years table as ALLNOBLANKROW ( 'Calendar'[Year] )

[gennaro_19]

Hi tamerjl,
Again, thank you for your attention to my case.
As you know, when a measure works, as soon as you type the apostrophe only, the DAX proposes the list of fields that the user can use, as in the image you see

Instead, at this apostrophe, the DAX does not propose anything to me, even if I try to ‘force’ and type despite the ban.

I think that the measure still does not work, even when writing the code.
😢

Thank you for what you can do.
🙏
g

[tamerj1]

@gennaro_19 

Can you please copy/paste your first formula?  I will correct it for you

[gennaro_19]

hi

tamerj1

here it is
but it does not work
😭
Annual Installment =

VAR CurrentYear = MAX('Calendar'[Year] )

VAR CumulativeTotal =

    CALCULATE(

        SUMX(

            FILTER(

                ALL('Regali'),

                YEAR('Regali'[Birth]) + 5 > CurrentYear && 'Regali'[Name] IN VALUES('Regali'[Name])

            ),

            'Regali'[Amount]),

         'Calendar'[Year] <= CurrentYear

    )

RETURN

CumulativeTotal

thks

[tamerj1]

@gennaro_19 

Annual Installment =
SUMX (
CROSSJOIN (
'Regali',
EXCEPT (
DISTINCT ( Years[Year] ),
GENERATESERIES (
YEAR ( 'Regali'[Birth] ),
YEAR ( 'Regali'[Birth] ) + 'Regali'[Duration] - 1,
1
)
)
),
'Regali'[Amount]
)

don't use the calendar table. Instead create Years table as suggested in one of my previous replies 

[gennaro_19]

hi tamerj1,

I'm really sorry, but the measure still doesn't work, despite having precisely your instructions (I created a separate table with the years).

Unfortunately, it is not possible to attach the pbix file on this platform, so you could see for yourself where the error occurs and why.

At this apostrophe, the DAX still does not propose anything to me.

Have you tried it in your own local file? Does it work for you?

Thanks again
g

[tamerj1]

@gennaro_19 

Why don't you just copy/paste the dax? Why are you rewriting it?  Also no need for any apostrophes at all as all table names are composed of a single word. Only 'Calendar' table requires apostrophes as the word Calendar is also reserved as a dax function. However, the dax proposed does not contain this table. 

on other hand you can upload your file to any cloud service and share the

 download link. 

[gennaro_19]

hi tamerj1,
You can find the file at this link.
https://drive.google.com/file/d/1MI34YNDrc-0ODEvNu7N7DM8WUHetZNIK/view?usp=drive_link

You are right, I would have had no reason to ‘write’ the code, just copy it.
But with just copying the text, look at the result

Thks
g

[tamerj1 · Accepted solution]

@gennaro_19 
Yes you are right. I've got a couple functions wrong.

Unfortunately, I was not able to download the file waiting for your approval to my download request.
Not sure why Years[Year] is marked as a wrong syntax. Have you already created the Years table?
Meanwhile you may try

Annual Installment =
SUMX (
    GENERATE (
        'Regali',
        INTERSECT (
            DISTINCT ( Years[Year] ),
            GENERATESERIES (
                YEAR ( 'Regali'[Birth] ),
                YEAR ( 'Regali'[Birth] ) + 'Regali'[Duration] - 1,
                1
            )
        )
    ),
    'Regali'[Amount]
)

*Update

I have been able to download the file. The forumla works fine, I just had to rename the Year table as Years. Attached the sample file with the solution

[gennaro_19 · Accepted solution]

hi @tamerj1 
Thank you very much,
You have been good and above all patient.
I am not that experienced and you have given me the opportunity to learn more. 🙏
🙏
 See you soon