Multiple If Conditions - Get Month and Year column from a Date column and do calculations

[E5254730]

I have a Request Date column, against this date column - I need to get a output as M/YYYY in a new column called as Start Date.

For e.g. if Requested Date column has 1-Jul-2025, then the output should be 7/2025 in Start Date columm

Secondly - I have added a long formula in excel in Current Year column (see below formula)

=IF(C2="1/2024",((A2/12)*0),IF(C2="2/2024",((A2/12)*1),IF(C2="3/2024",((A2/12)*2),IF(C2="4/2024",((A2/12)*3),IF(C2="5/2024",((A2/12)*4),IF(C2="6/2024",((A2/12)*5),IF(C2="7/2024",((A2/12)*6),IF(C2="7/2024",((A2/12)*6),IF(C2="8/2024",((A2/12)*7),IF(C2="9/2024",((A2/12)*8),IF(C2="10/2024",((A2/12)*9),IF(C2="11/2024",((A2/12)*10),IF(C2="12/2024",((A2/12)*11),IF(C2="1/2025",((A2/12)*12),IF(C2="2/2025",((A2/12)*11),IF(C2="3/2025",((A2/12)*10),IF(C2="4/2025",((A2/12)*9),IF(C2="5/2025",((A2/12)*8),IF(C2="6/2025",((A2/12)*7),IF(C2="7/2025",((A2/12)*6),IF(C2="8/2025",((A2/12)*5),IF(C2="9/2025",((A2/12)*4),IF(C2="10/2025",((A2/12)*3),IF(C2="11/2025",((A2/12)*2),IF(C2="12/2025",((A2/12)*1))))))))))))))))))))))))))

Basically how the calculation is done here, is to calculate "Actual $ Value"/12*Current month and remaining rest of the month in year

1. For e.g. Actual $ Value where it says - $3800,000 and the month is 7/2025 (In Start Date), so the calculation will be (380000/12)*6

2. For e.g. Actual $ Value where it says - $1837 and the month is 2/2025 (In Start Date), so the calculation will be (1837/12)*11

See the one's highligted in Orange

And if Actual $ Value column has a blank, return blank in Current Year column

Please advise how can we acieve both the logics in Power BI report?

Thanks,

[E5254730]

No, it should be:

If Start Date is Feb 2026, Feb and following months of 2026.

So the calculation will be Actual Value/12*11 months

[Khushidesai0109 · Accepted solution]

Hiii @E5254730 

Try this

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, 13 - StartMonth, BLANK() ) -- If 2026, take from start month onward
)

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

[E5254730]

@Khushidesai0109 

How about 2026 calculations?

[Khushidesai0109]

what is the logic for it?

same as 2025 or this 

1. In 2026 Calculated column use below logic: All Previous months of 2026. Do not consider Start Date
   which is provided earlier

[E5254730]

@Khushidesai0109 @rajendraongole1 

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]

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]

@Khushidesai0109 @rajendraongole1 

Thanks! for your solution. But I've messed with the logics during my initial explanation.

Hopefully if you can help with the extact logics I'm looking as below:

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

Sorry for any misunderstanding. Can you please advise further

I've also posted a new question at below link for the same requirement:

https://community.fabric.microsoft.com/t5/Desktop/Create-2-Calculated-columns-to-get-the-calculation...

If required, we can mark this thread as a solution and look for a solution at above link.

Your thoughts, please.

Thanks

[rajendraongole1]

Np, You can create the two calculated columns in Power BI using DAX based on your requirements.

as you mentioned, Since you've already created the Start Date column using

Start Date = FORMAT('Table'[Requested Date], "M/YYYY")

You may need to convert it back to a proper date format for calculations

Start Date (Date) = DATEVALUE("1/" & 'Table'[Start Date])

This should sum all values from the Start Date and previous months in 2025.

2025 Calculation =
VAR CurrentMonth = MONTH('Table'[Start Date (Date)])
VAR CurrentYear = YEAR('Table'[Start Date (Date)])
RETURN
IF(
CurrentYear = 2025,
CALCULATE(
SUM('Table'[Actual $ Value]),
'Table'[Start Date (Date)] <= EOMONTH('Table'[Start Date (Date)], 0),
YEAR('Table'[Start Date (Date)]) = 2025
),
0
)

Now create it for 2026,This should sum only previous months of 2026, excluding the Start Date itself, use calculated column :

2026 Calculation =
VAR CurrentMonth = MONTH('Table'[Start Date (Date)])
VAR CurrentYear = YEAR('Table'[Start Date (Date)])
RETURN
IF(
CurrentYear = 2026,
CALCULATE(
SUM('Table'[Actual $ Value]),
'Table'[Start Date (Date)] < EOMONTH('Table'[Start Date (Date)], -1),
YEAR('Table'[Start Date (Date)]) = 2026
),
0
)

I hope this helps. 

[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
)

[E5254730]

@Khushidesai0109 

My values does not seems to be matching using your above logic.

I need to have one more calculate column for 2026. The one that you provided is used for 2025.

Logic used fro 2025 is giving me correct values.

How can we achieve with a new calculate column for 2026?

Please advise. Thanks!

[Khushidesai0109]

@E5254730 

Next Year (2026) =
VAR StartMonth = MONTH( 'Table'[Requested Date] ) -- Extract Month
VAR StartYear = YEAR( 'Table'[Requested Date] ) -- Extract Year
VAR BaseYear = 2026 -- Change this if needed for future years

VAR MonthsRemainingNextYear =
IF( StartYear = BaseYear, 12 - StartMonth, -- If year = 2026, get remaining months
IF( StartYear = BaseYear - 1, StartMonth, BLANK() ) -- If year = 2025, get passed months
)

RETURN
IF( ISBLANK( 'Table'[Actual $ Value] ), BLANK(),
( 'Table'[Actual $ Value] / 12 ) * MonthsRemainingNextYear
)
Here you go!!

[E5254730]

@rajendraongole1 @Khushidesai0109 

Your logics seems to be working for Current year column.

There is one more column as Next Year, for which I'm using the below formula in Excel:

=IF(C2="1/2025",((A2/12)*0),IF(C2="2/2025",((A2/12)*1),IF(C2="3/2025",((A2/12)*2),IF(C2="4/2025",((A2/12)*3),IF(C2="5/2025",((A2/12)*4),IF(C2="6/2025",((A2/12)*5),IF(C2="7/2025",((A2/12)*6),IF(C2="7/2025",((A2/12)*6),IF(C2="8/2025",((A2/12)*7),IF(C2="9/2025",((A2/12)*8),IF(C2="10/2025",((A2/12)*9),IF(C2="11/2025",((A2/12)*10),IF(C2="12/2025",((A2/12)*11),IF(C2="1/2026",((A2/12)*12),IF(C2="2/2026",((A2/12)*11),IF(C2="3/2026",((A2/12)*10),IF(C2="4/2026",((A2/12)*9),IF(C2="5/2026",((A2/12)*8),IF(C2="6/2026",((A2/12)*7),IF(C2="7/2026",((A2/12)*6),IF(C2="8/2026",((A2/12)*5),IF(C2="9/2026",((A2/12)*4),IF(C2="10/2026",((A2/12)*3),IF(C2="11/2026",((A2/12)*2),IF(C2="12/2026",((A2/12)*1))))))))))))))))))))))))))

The previous formula had 2024 and 2025 which we used to get calculations in Current Year column

How can we achieve the logic for Next Year column with the above excel formula

Thanks! so far

[rajendraongole1]

Hi @E5254730  -You essentially need to calculate a scaling factor based on the month-year 

check the below:

Next_Year_Value =
VAR SelectedMonthYear = SELECTEDVALUE('Table'[C])
VAR BaseValue = 'Table'[A] / 12
VAR MonthIndex =
SWITCH( TRUE(),
SelectedMonthYear = "1/2025", 0,
SelectedMonthYear = "2/2025", 1,
SelectedMonthYear = "3/2025", 2,
SelectedMonthYear = "4/2025", 3,
SelectedMonthYear = "5/2025", 4,
SelectedMonthYear = "6/2025", 5,
SelectedMonthYear = "7/2025", 6,
SelectedMonthYear = "8/2025", 7,
SelectedMonthYear = "9/2025", 8,
SelectedMonthYear = "10/2025", 9,
SelectedMonthYear = "11/2025", 10,
SelectedMonthYear = "12/2025", 11,
SelectedMonthYear = "1/2026", 12,
SelectedMonthYear = "2/2026", 11,
SelectedMonthYear = "3/2026", 10,
SelectedMonthYear = "4/2026", 9,
SelectedMonthYear = "5/2026", 8,
SelectedMonthYear = "6/2026", 7,
SelectedMonthYear = "7/2026", 6,
SelectedMonthYear = "8/2026", 5,
SelectedMonthYear = "9/2026", 4,
SelectedMonthYear = "10/2026", 3,
SelectedMonthYear = "11/2026", 2,
SelectedMonthYear = "12/2026", 1,
BLANK() -- Default case
)

RETURN
BaseValue * MonthIndex

Hope this helps.

[Khushidesai0109]

@E5254730 
Yes! We can create a universal DAX formula that works for any year dynamically, not just for 2024, 2025, or 2026.

Yearly Calculation =
VAR StartMonth = MONTH( 'Table'[Requested Date] ) -- Extract Month
VAR StartYear = YEAR( 'Table'[Requested Date] ) -- Extract Year
VAR CurrentYear = YEAR( TODAY() ) -- Dynamically get the current year

VAR MonthsRemaining =
IF( StartYear = CurrentYear, 12 - StartMonth, -- Current Year → Remaining Months
IF( StartYear = CurrentYear - 1, StartMonth, -- Previous Year → Passed Months
IF( StartYear = CurrentYear + 1, 12 - StartMonth, -- Next Year → Remaining Months
BLANK() -- For other years, return BLANK (Modify as needed)
)
)
)

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


Did I answer your question? Mark my post as a solution!
Kuddos are Appreciated 

[Khushidesai0109]

Hiii @E5254730 
First 
Create "Start Date" Column
Start Date = FORMAT( 'Table'[Requested Date], "M/YYYY" )


Then 
Create "Current Year" Column Calculation

Current Year =
VAR StartMonth = MONTH( 'Table'[Requested Date] ) -- Extract Month
VAR StartYear = YEAR( 'Table'[Requested Date] ) -- Extract Year
VAR BaseYear = 2025 -- Change this if your base year changes

VAR MonthsRemaining =
IF( StartYear = BaseYear, 12 - StartMonth,
IF( StartYear = BaseYear - 1, StartMonth, BLANK() )
)

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

Did I answer your question? Mark my post as a solution!
Kuddos are Appreciated 

[rajendraongole1]

Hi @E5254730  - you can create two calculated columns 

first one, Start Date = FORMAT('Table'[Requested Date], "M/YYYY")

another one for current year as below:

Current Year =
VAR _StartMonth = MONTH('Table'[Requested Date]) // Extracts month from Requested Date
VAR _Year = YEAR('Table'[Requested Date]) // Extracts year from Requested Date
VAR _CurrentYear = 2025 // Set to 2025 (modify if needed)
VAR _MonthsRemaining = 12 - (_StartMonth - 1) // Months remaining in 2025
VAR _MonthlyValue = DIVIDE('Table'[Actual $ Value], 12) // Divide by 12 months

RETURN
IF(
ISBLANK('Table'[Actual $ Value]),
BLANK(), // Return blank if Actual $ Value is blank
_MonthlyValue * _MonthsRemaining
)

I hope this works in your scenerio.

[E5254730]

@rajendraongole1  @Khushidesai0109 

Is this only related to 2025 dates?

If you look into the excel formula that I provided, it also has 2024 dates. And next year it will be 2025 and 2026 (2024 dates will not be there in next year)

Can you please provide me the PBI report, if you may have created.

Thanks!

[Khushidesai0109]

hii @E5254730 

Current Year =
VAR StartMonth = MONTH( 'Table'[Requested Date] ) -- Extract Month
VAR StartYear = YEAR( 'Table'[Requested Date] ) -- Extract Year
VAR CurrentYear = YEAR( TODAY() ) -- Dynamically get the current year

VAR MonthsRemaining =
IF( StartYear = CurrentYear, 12 - StartMonth,
IF( StartYear = CurrentYear - 1, StartMonth, BLANK() )
)

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

this might help
Please kudos are appreicated and Accept my reply as solution if I provided correct formula