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

Anonymous — Thu, 10 Oct 2019 11:34:56 GMT

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

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

technolog — Sat, 08 Jun 2024 01:57:15 GMT

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"
)

topic Dax to calculate last 12 month and Current month flag for the local time zone in DAX Commands and Tips

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

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