Forum Discussion

hmeltonPSL's avatar
hmeltonPSL
Icon for Helper II rankHelper II
4 years ago
Solved

Change Date range in a Calendar Table

I would like to extend the date range in my calendar table formula below. Calendar = CALENDAR(DATE(2016,1,1),DATE(2021,12,31))   I changed the end date year to 2025 but when I save it I get the f...
  • WinterMist's avatar
    WinterMist
    4 years ago

    hmeltonPSL 

     

    Good news!  I think I found the answer.

     

    Delete these 2 columns from the TenantMailboxUsage table:

    1) "TotalIssueWarningQuota(KB/MB/GB/TB)"

    2) "StorageUsed(KB/MB/GB/TB)"

     

    Doing this causes the circular dependency error to disappear when changing the relationship from 1:1 to *:1 between the TenantUsageMailbox & Calendar tables.

     

    Once the above steps were taken, I was finally able to edit the CALENDAR formula, and the USERELATIONSHIP error no longer appears!  (Changed end year from 2025 to 2022 as a test, and it worked.)

     

     

    Hopefully this resolves both errors for you as it did for me!

     

    Regards,

    Nathan

     

    P.S. Solution found here.

    https://github.com/MicrosoftDocs/microsoft-365-docs/issues/4056

WinterMist's avatar
WinterMist
Icon for Impactful Individual rankImpactful Individual
4 years ago

hmeltonPSL 

 

Excellent!  So glad it worked for you.

 

Additionally, I have been looking for a way to NOT have to update the Calendar code over time.

Similar to other users, we don't want the limitations of CALENDARAUTO.

However, with CALENDAR, we don't want a hard-coded end date either.

 

That same post I shared also taught me how to do this, although I believe his end-date had a typo.

Start date MUST be 1/1, and end date MUST be 12/31 in DAX calendar for time intelligence to work properly.

 

I'm going to use something like this from now on, so I won't have to keep updating the end date year.

 

 

Hope this is helpful to you as it was to me.

 

Regards,

Nathan