Resolving Subtraction issues following change of Year.

[MauriceBrunsEco]

I had modified my Calender table; to year 2025 dates - but noticed that the Month / Week Subtraction formulas are not providing the correct outcome.

The date list was adjusted to the following, to show 2025.

= List.Dates(#date(2024,1,1),730, #duration(1,0,0,0))

Extract on the Month Subtraction outcome. 

Extract on the Week Subtraction outcome. 

How would I resolve these issues? 

[FarhanJeelani · Accepted solution]

It sounds like modifying the calendar table to include 2025 dates has caused issues with the "Month Subtraction" and "Week Subtraction" columns, likely due to how the year transition is handled in these calculations. Here’s a detailed approach to help resolve these issues:

 1. Verify Date Range in the Calendar Table

Your calendar table currently generates dates starting from January 1, 2024, for 730 days, which covers 2024 and 2025. Ensure that any calculated columns relying on this calendar table, such as "Current Month" or "Current Week," dynamically reference the dates in this table rather than relying on hardcoded year values.

2. Review and Adjust Month Subtraction Formula

For the "Month Subtraction" column, the formula should correctly handle year changes, especially when comparing months across different years. Here’s how you can approach it:

- Option 1: Use DATEDIFF with Month Level
Use `DATEDIFF` to calculate the number of months between two dates. For instance:
DAX

Month Subtraction = DATEDIFF([Start of Month], [Current Month], MONTH)

This method calculates the difference in months even when the dates span different years.

- Option 2: Adjust with Year and Month Components
If you’re working with year and month columns separately, ensure the formula accounts for year differences. For example:
DAX

Month Subtraction = ([Year of Current Month] - [Year of Start Month]) * 12 + ([Current Month] - [Start of Month])

This formula calculates the month difference while considering the year change.

3. Review and Adjust Week Subtraction Formula

For the "Week Subtraction" column, ensure the formula correctly handles weeks across year boundaries. Here are some approaches:

- Option 1: DATEDIFF with Week Level
Similar to the month difference, you can use `DATEDIFF` at the week level:
DAX

Week Subtraction = DATEDIFF([Start of Week], [Current Week], WEEK)

This calculates the difference in weeks between two dates.

- Option 2: Calculate Week Difference Manually
If you’re calculating the difference based on week numbers, make sure to account for the year. Here’s an example:
DAX

Week Subtraction = ([Year of Current Week] - [Year of Start Week]) * 52 + ([Current Week] - [Start of Week])

This method assumes 52 weeks per year. If your calendar is structured differently, you may need to adjust for 53 weeks or for partial weeks.

4. Test the Calculations Across Year Boundaries

After implementing these adjustments, verify that the "Month Subtraction" and "Week Subtraction" columns calculate correctly when dates span from December of one year to January of the next. You might want to specifically test boundary cases, such as:- End of December 2024 to January 2025 for month and week transitions
- End of one week in 2024 to the start of a week in 2025

5. Update Dependencies and Refresh the Data Model

If other columns or measures depend on these calculations, make sure to update and test them as well. After making these changes, refresh your data model to ensure the calculations update accordingly.

Example of Adjusted DAX Calculations

Here’s how the adjusted formulas might look in DAX:

DAX

// Month Subtraction Calculation with Year Adjustment
Month Subtraction =
([Year of Current Month] - [Year of Start Month]) * 12 +
([Current Month] - [Start of Month])

// Week Subtraction Calculation with Year Adjustment
Week Subtraction =
([Year of Current Week] - [Year of Start Week]) * 52 +
([Current Week] - [Start of Week])

These formulas help account for transitions across years by incorporating both the year and month/week number.

 Final Testing

Once these adjustments are made, review the outputs to confirm that they are now correct for all months and weeks, especially around year-end. If the issue persists or if the calculations don’t behave as expected, please share the specific formulas you're using, and I can assist with further refinements.

[Anonymous · Accepted solution]

Hi,

Thanks for the solution FarhanJeelani  and lbendlin offered and i want to offer some more informaiton for user to refer to.

hello @MauriceBrunsEco , it is better that you use the data with date type, then  calculate, you can refet to the following sample.

Sample data

Output

You can put the following code to advanced editor to refer to.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtU31DdU0gExTfQNQexYHaiEES4JY3SJWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start Month" = _t, #"Current Month" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Month", type date}, {"Current Month", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Month_sub", each Number.Round(Number.From(Duration.Days([Start Month]-[Current Month])/( 365.25 / 12 )) ,0 )),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Week_sub", each Number.Round(Duration.Days([Start Month]-[Current Month])/7))
in
    #"Added Custom1"

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[FarhanJeelani · Accepted solution]

It sounds like modifying the calendar table to include 2025 dates has caused issues with the "Month Subtraction" and "Week Subtraction" columns, likely due to how the year transition is handled in these calculations. Here’s a detailed approach to help resolve these issues:

 1. Verify Date Range in the Calendar Table

Your calendar table currently generates dates starting from January 1, 2024, for 730 days, which covers 2024 and 2025. Ensure that any calculated columns relying on this calendar table, such as "Current Month" or "Current Week," dynamically reference the dates in this table rather than relying on hardcoded year values.

2. Review and Adjust Month Subtraction Formula

For the "Month Subtraction" column, the formula should correctly handle year changes, especially when comparing months across different years. Here’s how you can approach it:

- Option 1: Use DATEDIFF with Month Level
Use `DATEDIFF` to calculate the number of months between two dates. For instance:
DAX

Month Subtraction = DATEDIFF([Start of Month], [Current Month], MONTH)

This method calculates the difference in months even when the dates span different years.

- Option 2: Adjust with Year and Month Components
If you’re working with year and month columns separately, ensure the formula accounts for year differences. For example:
DAX

Month Subtraction = ([Year of Current Month] - [Year of Start Month]) * 12 + ([Current Month] - [Start of Month])

This formula calculates the month difference while considering the year change.

3. Review and Adjust Week Subtraction Formula

For the "Week Subtraction" column, ensure the formula correctly handles weeks across year boundaries. Here are some approaches:

- Option 1: DATEDIFF with Week Level
Similar to the month difference, you can use `DATEDIFF` at the week level:
DAX

Week Subtraction = DATEDIFF([Start of Week], [Current Week], WEEK)

This calculates the difference in weeks between two dates.

- Option 2: Calculate Week Difference Manually
If you’re calculating the difference based on week numbers, make sure to account for the year. Here’s an example:
DAX

Week Subtraction = ([Year of Current Week] - [Year of Start Week]) * 52 + ([Current Week] - [Start of Week])

This method assumes 52 weeks per year. If your calendar is structured differently, you may need to adjust for 53 weeks or for partial weeks.

4. Test the Calculations Across Year Boundaries

After implementing these adjustments, verify that the "Month Subtraction" and "Week Subtraction" columns calculate correctly when dates span from December of one year to January of the next. You might want to specifically test boundary cases, such as:- End of December 2024 to January 2025 for month and week transitions
- End of one week in 2024 to the start of a week in 2025

5. Update Dependencies and Refresh the Data Model

If other columns or measures depend on these calculations, make sure to update and test them as well. After making these changes, refresh your data model to ensure the calculations update accordingly.

Example of Adjusted DAX Calculations

Here’s how the adjusted formulas might look in DAX:

DAX

// Month Subtraction Calculation with Year Adjustment
Month Subtraction =
([Year of Current Month] - [Year of Start Month]) * 12 +
([Current Month] - [Start of Month])

// Week Subtraction Calculation with Year Adjustment
Week Subtraction =
([Year of Current Week] - [Year of Start Week]) * 52 +
([Current Week] - [Start of Week])

These formulas help account for transitions across years by incorporating both the year and month/week number.

 Final Testing

Once these adjustments are made, review the outputs to confirm that they are now correct for all months and weeks, especially around year-end. If the issue persists or if the calculations don’t behave as expected, please share the specific formulas you're using, and I can assist with further refinements.

[lbendlin]

Do proper date math, instead of trying to use integers.  Or multiply the year by 12 and add the month if you need a surrogate integer key