Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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
Solved! Go to Solution.
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"
)
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"
)
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
21 | |
15 | |
14 | |
11 | |
7 |
User | Count |
---|---|
26 | |
24 | |
12 | |
11 | |
10 |