Forum Discussion
Helper IICreate 2 Calculated columns to get the calculations based on date column
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
- In 2025 Calculated column use below logic: Start Date + all previous months of 2025
- 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!
Hiii E5254730
This might help youCurrent Year (2025) =
VAR StartMonth = MONTH( 'Table'[Requested Date] )
VAR StartYear = YEAR( 'Table'[Requested Date] )
VAR BaseYear = 2025VAR 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!
4 Replies
- Khushidesai0109
Skilled Sharer
Hiii E5254730
DAX for 2025 Calculation
Current Year (2025) =
VAR StartMonth = MONTH( 'Table'[Requested Date] )
VAR StartYear = YEAR( 'Table'[Requested Date] )
VAR BaseYear = 2025VAR 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 CalculationNext Year (2026) =
VAR StartMonth = MONTH( 'Table'[Requested Date] )
VAR StartYear = YEAR( 'Table'[Requested Date] )
VAR BaseYear = 2026VAR 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
Super User
E5254730 Create using
2025 Calculated =
VAR StartMonth = MONTH('Table'[Requested Date])
VAR EndMonth = 12
VAR MonthsIn2025 = EndMonth - StartMonth + 1
RETURN
('Table'[Actual $ Value] / 12) * MonthsIn2025DAX
2026 Calculated =
VAR StartMonth = MONTH('Table'[Requested Date])
VAR MonthsIn2026 = 12 - StartMonth
RETURN
('Table'[Actual $ Value] / 12) * MonthsIn2026 
E5254730I just realized, I made a typo error in below statement:
- 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
Hiii E5254730
This might help youCurrent Year (2025) =
VAR StartMonth = MONTH( 'Table'[Requested Date] )
VAR StartYear = YEAR( 'Table'[Requested Date] )
VAR BaseYear = 2025VAR 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!
