topic Re: dax formula to allocate income to future dates in DAX Commands and Tips

dax formula to allocate income to future dates

Auto_queen — Tue, 19 Dec 2023 00:34:04 GMT

I'm having a difficult time with this and I don't know why. At the beginning of each year people get awarded units. They vest over 4 years starting the next year, 25%/year. I want to be able to show in a matrix table the amount of units vested per year even in the future, and I'm having trouble.
I've added two calculated columns for Vesting Start Date and Vesting End Date. I've connected my table to a Date table. Nothing is working.

Here is an example of my table:

Grant ID	Grant Date	User	Units Awarded
1	1/1/2020	User1	2500
2	1/1/2021	User1	5000
3	1/1/2022	User1	8000
1	1/1/2020	User2	1000
2	1/1/2021	User2	2500

Here is an example of what I want to see: Filtered by User1

Grant ID	2021	2022	2023	2024	2025	2026
1	625	625	625	625
2		1250	1250	1250	1250
3			2000	2000	2000	2000

I tried this formula in addition to others but nothing is working.

Vested Units =

VAR _year1 = DATEADD('User Units'[Grant Date],1,YEAR)

VAR _year4 = DATEADD('User Units'[Grant Date],4,YEAR)

VAR _vestedunits = [Total Units Awarded] *.25

RETURN

IF(SELECTEDVALUE(Table1[Vesting Dates].[Date]) >= _year1 || SELECTEDVALUE(Table1[Vesting Dates].[Date]) <= _year4, [Total Units Awarded]*.25,"")

Re: dax formula to allocate income to future dates

Jihwan_Kim — Tue, 19 Dec 2023 03:55:06 GMT

Hi,

Please check the below picture and the attached pbix file if it suits your requirement.

expected result measure: = SUMX ( CROSSJOIN ( SUMMARIZE ( 'User Units', 'User'[User], 'User Units'[Grant ID] ), VALUES ( 'Calendar'[Year] ) ), CALCULATE ( VAR _awardtyear = YEAR ( MAX ( 'User Units'[Grant Date] ) ) VAR _startyear = _awardtyear + 1 VAR _finishyear = _awardtyear + 4 VAR _award = SUM ( 'User Units'[Units Awarded] ) VAR _25percent = _award / 4 RETURN IF ( MAX ( 'Calendar'[Year] ) >= _startyear && MAX ( 'Calendar'[Year] ) <= _finishyear, _25percent ) ) )

Re: dax formula to allocate income to future dates

Fowmy — Tue, 19 Dec 2023 09:50:29 GMT

Auto_queen

Please find below the allocation measure which also considers the Dates table relationship to filter the allocation years.

Allocation = var __years = VALUES(Dates[Year]) RETURN CALCULATE( SUMX( ADDCOLUMNS( GENERATE( SUMMARIZE( Table1 , Table1[User] , Table1[Grant ID] , Table1[Grant Date]), FILTER( GENERATESERIES( YEAR( Table1[Grant Date])+1 , YEAR( Table1[Grant Date])+4 ), [Value] in __years ) ), "Amount",DIVIDE( CALCULATE(SUM(Table1[Units Awarded])), 4 ) ), [Amount] ), REMOVEFILTERS(Dates) )

File attached below.

Re: dax formula to allocate income to future dates

Auto_queen — Tue, 19 Dec 2023 17:05:21 GMT

Thank you so much! Both ways worked! I learned a lot from your work. Thank you!