Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Anonymous
Not applicable

Dax to calculate last 12 month and Current month flag for the local time zone

Hi, 

I have to create the calculated columns for the Last 12 months and Current Month flag, Date ranges should be calculated based on the user timezone. 

 

Eg. If the user is in Australia on the end of month he might be in next month 'November' for the UTC date whereas UK user current month could be 'October' only.

 

I have below tables with the column names: 

Date                            Sales 

Calenderdate              Date  Amount   Country   Timezone   CurrentLocalTime  

 

I have tried mulitple ways but not succeded yet, any help on this much appreciated.

 

Thanks,

Pankaj

 

1 ACCEPTED SOLUTION
technolog
Super User
Super User

To handle time zone differences and create calculated columns for the last 12 months and current month flags based on user time zones, you need to adjust your calculations based on the local time. Here's how you can achieve this:

Step 1: Adjust for Local Time
First, create a calculated column that adjusts the Calenderdate to the local time based on the Timezone information.

Local DateTime Calculation
Assuming you have the Timezone as the UTC offset in hours, you can create a column to adjust for local time:

LocalDateTime =
DATEADD(
'Sales'[Calenderdate],
'Sales'[Timezone],
HOUR
)
Step 2: Current Month Flag
Create a calculated column to flag the current month based on the local date:

CurrentMonthFlag =
VAR LocalDate = 'Sales'[LocalDateTime]
VAR CurrentLocalDate = TODAY() + TIMEVALUE('Sales'[Timezone] / 24)
RETURN
IF(
YEAR(LocalDate) = YEAR(CurrentLocalDate) && MONTH(LocalDate) = MONTH(CurrentLocalDate),
"Current Month",
"Not Current Month"
)
Step 3: Last 12 Months Flag
Create a calculated column to flag dates within the last 12 months based on the local date:

Last12MonthsFlag =
VAR LocalDate = 'Sales'[LocalDateTime]
VAR CurrentLocalDate = TODAY() + TIMEVALUE('Sales'[Timezone] / 24)
RETURN
IF(
LocalDate >= EDATE(CurrentLocalDate, -12) && LocalDate < CurrentLocalDate,
"Last 12 Months",
"Not Last 12 Months"
)

View solution in original post

1 REPLY 1
technolog
Super User
Super User

To handle time zone differences and create calculated columns for the last 12 months and current month flags based on user time zones, you need to adjust your calculations based on the local time. Here's how you can achieve this:

Step 1: Adjust for Local Time
First, create a calculated column that adjusts the Calenderdate to the local time based on the Timezone information.

Local DateTime Calculation
Assuming you have the Timezone as the UTC offset in hours, you can create a column to adjust for local time:

LocalDateTime =
DATEADD(
'Sales'[Calenderdate],
'Sales'[Timezone],
HOUR
)
Step 2: Current Month Flag
Create a calculated column to flag the current month based on the local date:

CurrentMonthFlag =
VAR LocalDate = 'Sales'[LocalDateTime]
VAR CurrentLocalDate = TODAY() + TIMEVALUE('Sales'[Timezone] / 24)
RETURN
IF(
YEAR(LocalDate) = YEAR(CurrentLocalDate) && MONTH(LocalDate) = MONTH(CurrentLocalDate),
"Current Month",
"Not Current Month"
)
Step 3: Last 12 Months Flag
Create a calculated column to flag dates within the last 12 months based on the local date:

Last12MonthsFlag =
VAR LocalDate = 'Sales'[LocalDateTime]
VAR CurrentLocalDate = TODAY() + TIMEVALUE('Sales'[Timezone] / 24)
RETURN
IF(
LocalDate >= EDATE(CurrentLocalDate, -12) && LocalDate < CurrentLocalDate,
"Last 12 Months",
"Not Last 12 Months"
)

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.