Issues Calculating Offset Calendar

[UnknownUser1702]

I have an intersting dilemma.

There is an Account table that has a custom_start_month and custom_fiscal_year values.  custom_fiscal year can be current year or next year (next year moves the year forward)
I have 3 fact tables with dates that have many to 1 relationship with the account table.
i have a date dimension that is one to many to the 3 fact tables.

I currently have calculated the adjusted year/quarter in each fact table based on the relationship to account.  However, when trying to join all the fact tables to gether for a summary, i cannot use those individual fact table adjustements.

However, i need to find a way to do this in the date dimension, even though there is no link.

Some examples are:
Account.Custom_Start_Month = 7 (july), Account.custom_fiscal year = 'Current Year".  In this scenario, it is basically saying that July is Q1 and it is in the current year
I want Date.AdjustedYear for 1/1/2024 to be. 2023.   In fact, 2023, would be the value for any actual dates between 7/1/2023 and 6/30/2024.
Account.Custom_Start_Month = 4 (april), Account_custom_fiscal year = 'Next Year'.  In this scenario, it is basically saying that April is Q1 and it is in the Next Year
I want Date.Adjusted Year for 1/1/2024 would be 2024.  Even though 1/1 would be Q4, they are a forward fiscal calendar year.  In fact, it would be 2024 for any dates, 4/1/2023-3/31/2024.  It would be 2025 for any dates betwen, 4/1/2024-and3/31/-2025.  

[Anonymous · Accepted solution]

Hi, @UnknownUser1702 

Based on your information, I create a sample table:

Then create calculated columns, here is the following DAX:

AdjustedYear current = 
VAR CurrentYear = YEAR('Date'[Date])
VAR CurrentMonth = MONTH('Date'[Date])
VAR AdjustedYear = 
    IF(
        MAX('Account'[Custom_Fiscal_Year]) = "Current Year",
        IF(CurrentMonth >= MAX('Account'[Custom_Start_Month]), CurrentYear + 1, CurrentYear),
        IF(CurrentMonth >= MAX('Account'[Custom_Start_Month]), CurrentYear, CurrentYear - 1)
        
    )
RETURN AdjustedYear

AdjustedYear next = 
VAR CurrentYear = YEAR('Date'[Date])
VAR CurrentMonth = MONTH('Date'[Date])
VAR AdjustedYear = 
    IF(
        MAX('Account'[Custom_Fiscal_Year]) = "Next Year",
        IF(CurrentMonth >= MIN('Account'[Custom_Start_Month]), CurrentYear + 1, CurrentYear),
        IF(CurrentMonth >= MIN('Account'[Custom_Start_Month]), CurrentYear, CurrentYear - 1)
        
    )
RETURN AdjustedYear

I've created a few other calculated columns to compare, such as the largest start month, and so on. Here is my preview:

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data)

Best Regards

Yongkang Hua

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

[UnknownUser1702]

Unfortunately this doesnt help either.  My account table has the start month and fiscal year.  My fact table has the actual dates and the date table has the calendar info.  There is no relationship between account and date as there are no overlaping attributes between account and date.  My date table has only 1 record per calendar date.

[Shravan133]

To handle the scenario where you need to adjust the year and quarter in your date dimension based on account-specific custom start months and fiscal years, you can create a calculated column in your Date dimension table that dynamically adjusts the year and quarter according to the rules defined by each account's custom_start_month and custom_fiscal_year.

Here's a step-by-step approach to achieving this in Power BI:

1. Add a Custom Adjusted Year Column to the Date Dimension:
• First, add a calculated column to the Date table that calculates the adjusted year based on the custom fiscal year start month and fiscal year type from the Account table.
• This calculation should use the account information to determine the correct adjusted year for each date.
2. Create a Relationship to Use the Account Table's Values:
• Create a many-to-many relationship between the Date table and the Account table using a bridge table if necessary. This allows you to use account-specific values in your date calculations.
3. DAX Calculations:
• Use DAX to calculate the adjusted year and quarter for each date in the Date table based on the custom fiscal year settings.

Here's how you can achieve this:

Step 1: Create Calculated Columns in the Date Table

Add calculated columns in the Date table to handle the adjusted fiscal year and quarter calculations:

AdjustedYear =

VAR customStartMonth = MAXX(RELATEDTABLE(Account), Account[custom_start_month])

VAR customFiscalYearType = MAXX(RELATEDTABLE(Account), Account[custom_fiscal_year])

VAR dateMonth = MONTH('Date'[Date])

VAR dateYear = YEAR('Date'[Date])

RETURN

IF(

    customFiscalYearType = "Next Year",

    IF(dateMonth < customStartMonth, dateYear + 1, dateYear),

    IF(dateMonth < customStartMonth, dateYear - 1, dateYear)

)

AdjustedQuarter =

VAR customStartMonth = MAXX(RELATEDTABLE(Account), Account[custom_start_month])

VAR dateMonth = MONTH('Date'[Date])

RETURN

SWITCH(

    TRUE(),

    dateMonth >= customStartMonth && dateMonth < customStartMonth + 3, 1,

    dateMonth >= customStartMonth + 3 && dateMonth < customStartMonth + 6, 2,

    dateMonth >= customStartMonth + 6 && dateMonth < customStartMonth + 9, 3,

    4

)

Step 2: Create Relationships

Ensure you have relationships set up correctly:

• Date to Fact Tables: One-to-many relationships based on the date field.
• Account to Fact Tables: Many-to-one relationships based on the account key.

Step 3: Use the Calculated Columns in Your Reports

Once you have the AdjustedYear and AdjustedQuarter columns in the Date table, you can use them in your reports and visuals. For example, you can create a summary table that aggregates data based on these adjusted columns.

SummaryTable =

SUMMARIZE(

    'FactTable',

    'Date'[AdjustedYear],

    'Date'[AdjustedQuarter],

    "TotalAmount", SUM('FactTable'[Amount])

)

Example Usage

• If an account has a custom start month of July (7) and the fiscal year is the current year:
• For a date like 1/1/2024, the AdjustedYear will be 2023.
• For a date like 7/1/2023, the AdjustedYear will be 2023.
• If an account has a custom start month of April (4) and the fiscal year is the next year:
• For a date like 1/1/2024, the AdjustedYear will be 2024.
• For a date like 4/1/2023, the AdjustedYear will be 2024.

Final Thoughts

This solution relies on having a clear understanding of the custom fiscal year rules and accurately implementing them in the DAX calculations. By doing so, you can ensure that your Date dimension dynamically adjusts based on the account-specific custom start month and fiscal year, allowing for accurate reporting and analysis in Power BI.

[UnknownUser1702]

Thank you for looking into this.  Creating a bridge table is not a viable option considering the number of accounts and the number of dates.  This bridge table would be extremely large and this would not be performant.  Maintanance would also be a nightmare to do as well.
Any other suggestions?