topic Re: Grandfather asks for a report on the birthday gift plan in DAX Commands and Tips

Grandfather asks for a report on the birthday gift plan

gennaro_19 — Fri, 27 Sep 2024 19:09:20 GMT

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

Re: Grandfather asks for a report on the birthday gift plan

tamerj1 — Fri, 27 Sep 2024 19:33:51 GMT

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

Re: Grandfather asks for a report on the birthday gift plan

gennaro_19 — Sat, 28 Sep 2024 10:20:48 GMT

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

Re: Grandfather asks for a report on the birthday gift plan

tamerj1 — Sun, 29 Sep 2024 08:29:33 GMT

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

Re: Grandfather asks for a report on the birthday gift plan

gennaro_19 — Sun, 29 Sep 2024 08:46:38 GMT

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

Re: Grandfather asks for a report on the birthday gift plan

tamerj1 — Sun, 29 Sep 2024 10:16:01 GMT

gennaro_19

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

Re: Grandfather asks for a report on the birthday gift plan

gennaro_19 — Sun, 29 Sep 2024 11:02:17 GMT

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

Re: Grandfather asks for a report on the birthday gift plan

tamerj1 — Sun, 29 Sep 2024 11:38:31 GMT

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

Re: Grandfather asks for a report on the birthday gift plan

gennaro_19 — Sun, 29 Sep 2024 12:06:34 GMT

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

Re: Grandfather asks for a report on the birthday gift plan

tamerj1 — Sun, 29 Sep 2024 12:17:05 GMT

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.

Re: Grandfather asks for a report on the birthday gift plan

gennaro_19 — Sun, 29 Sep 2024 13:45:13 GMT

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

Re: Grandfather asks for a report on the birthday gift plan

tamerj1 — Sun, 29 Sep 2024 18:37:43 GMT

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

Re: Grandfather asks for a report on the birthday gift plan

gennaro_19 — Sun, 29 Sep 2024 19:28:50 GMT

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