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!

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

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