Create 2 Calculated columns to get the calculations based on date column

[E5254730]

Hello,

Column D and E are values calculated based on Start Date column. We can ignore column C from the Excel, because I've created a "Start Date" Column in PBI using below logic:
Start Date = FORMAT( 'Table'[Requested Date], "M/YYYY" ) 

Now I need to have 2 calculated columns in PBI to get the results as shown in above screen shot

1. In 2025 Calculated column use below logic: Start Date + all previous months of 2025
2. In 2026 Calculated column use below logic: All Previous months of 2026. Do not consider Start Date

Excel formulas explained in column F and G.

Please advise. Thanks!

[E5254730]

@bhanu_gautam @Khushidesai0109 

I just realized, I made a typo error in below statement:

1. In 2025 Calculated column use below logic: Start Date + all previous months of 2025

It should be Start Date + all following months of 2025

For Instance if it's March 2025, then the calculations will be for 10 months (Mar to Dec 2025)

This came to my notice after applying the DAX provided by both of you, as the values were not matching with my table been provided

[Khushidesai0109 · Accepted solution]

Hiii @E5254730 

This might help you

Current Year (2025) =
VAR StartMonth = MONTH( 'Table'[Requested Date] )
VAR StartYear = YEAR( 'Table'[Requested Date] )
VAR BaseYear = 2025

VAR MonthsRemaining =
IF( StartYear = BaseYear, 13 - StartMonth, -- From Start Date to December (10 for March, etc.)
IF( StartYear = BaseYear - 1, 12, BLANK() ) -- If 2024, take full year of 2025
)

RETURN
IF( ISBLANK( 'Table'[Actual $ Value] ), BLANK(),
( 'Table'[Actual $ Value] / 12 ) * MonthsRemaining
)

If this helps, I would appreciate your KUDOS!
Did I answer your question? Mark my post as a solution!

[Khushidesai0109]

Hiii @E5254730 

DAX for 2025 Calculation

Current Year (2025) =
VAR StartMonth = MONTH( 'Table'[Requested Date] )
VAR StartYear = YEAR( 'Table'[Requested Date] )
VAR BaseYear = 2025

VAR MonthsCovered =
IF( StartYear = BaseYear, StartMonth, -- Include Start Date + Previous Months
IF( StartYear = BaseYear - 1, 12, BLANK() ) -- If 2024, include full year of 2025
)

RETURN
IF( ISBLANK( 'Table'[Actual $ Value] ), BLANK(),
( 'Table'[Actual $ Value] / 12 ) * MonthsCovered
)


DAX for 2026 Calculation

Next Year (2026) =
VAR StartMonth = MONTH( 'Table'[Requested Date] )
VAR StartYear = YEAR( 'Table'[Requested Date] )
VAR BaseYear = 2026

VAR MonthsCoveredNextYear =
IF( StartYear = BaseYear - 1, 12, -- If 2025, take full year
IF( StartYear = BaseYear, StartMonth - 1, BLANK() ) -- If 2026, take only previous months
)

RETURN
IF( ISBLANK( 'Table'[Actual $ Value] ), BLANK(),
( 'Table'[Actual $ Value] / 12 ) * MonthsCoveredNextYear
)

If this helps, I would appreciate your KUDOS!
Did I answer your question? Mark my post as a solution!

[bhanu_gautam]

@E5254730 Create using

2025 Calculated =
VAR StartMonth = MONTH('Table'[Requested Date])
VAR EndMonth = 12
VAR MonthsIn2025 = EndMonth - StartMonth + 1
RETURN
('Table'[Actual $ Value] / 12) * MonthsIn2025

DAX
2026 Calculated =
VAR StartMonth = MONTH('Table'[Requested Date])
VAR MonthsIn2026 = 12 - StartMonth
RETURN
('Table'[Actual $ Value] / 12) * MonthsIn2026