Forum Discussion

E5254730's avatar
E5254730
Icon for Helper II rankHelper II
1 year ago
Solved

Create 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 = FORM...
Need Help
  • Khushidesai0109's avatar
    Khushidesai0109
    1 year ago

    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's avatar
Khushidesai0109
Icon for Skilled Sharer rankSkilled Sharer
1 year ago

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!