Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi,
I have to findout localdate from the date table based on the timezone matched with the fact table. I am using below dax but its not working.
CurrentDay = CALCULATE (VALUES(CalendarDate[LocalDateTime]),
USERELATIONSHIP(Sales[TimeZone],CalendarDate[TimeZone] ))
I have the calendarDate table having multiple sets of dates for each timezone and Current LocalDateTime calculated based on the timezone
In fact table i have getting amount value with the timezone and localtime for the timezone for that country.
I have to calculate Last 12 months Flag in Calendar Date table based on the Country selected from the Sales table in the report filter.
I have added active relationship on CalendarDate - "Date" column with Sales - "Date" column
and inactive relationship on "TimeZone" column
Below is the table schema :
CalendarDate Sales
Date TimeZone LocalTime Date TimeZone LocalTime Country
1/1/2019 GMT 4/10/2019 1/10/2019 GMT 4/10/2019 UK
-------- IST 5/10/2019 ------- IST 5/10/2019 INDIA
----
Thanks,
Solved! Go to Solution.
To achieve the required calculations using the inactive relationship in DAX, you need to activate the relationship within the calculation. Here’s how you can properly set up the calculation for the CurrentDay and subsequently use it to flag the last 12 months based on the country selected.
Step 1: Correct Calculation of CurrentDay
The DAX expression you provided should work with USERELATIONSHIP, but you need to ensure it’s properly structured. Also, since the VALUES function returns a table, you might want to use SELECTEDVALUE or MIN/MAX if you expect a single value.
CurrentDay =
CALCULATE(
SELECTEDVALUE(CalendarDate[LocalDateTime]),
USERELATIONSHIP(Sales[TimeZone], CalendarDate[TimeZone])
)
Step 2: Flagging Last 12 Months
Next, you need to create the flag for the last 12 months in the CalendarDate table based on the country selected. Ensure that you are correctly using the CurrentDay calculated column.
Adjusting the Date Column for the Last 12 Months
You’ll need a calculated column or measure to determine if the date falls within the last 12 months from the CurrentDay.
Calculated Column for Last 12 Months Flag
First, ensure your CurrentDay calculation works and reflects the correct local date. Then, use this value to create a flag for the last 12 months.
Step 3: Create the Last 12 Months Flag in CalendarDate
Create a calculated column in the CalendarDate table to flag dates within the last 12 months based on the local date:
Last12MonthsFlag =
VAR CurrentLocalDate =
CALCULATE(
MAX(Sales[LocalTime]),
USERELATIONSHIP(Sales[TimeZone], CalendarDate[TimeZone])
)
RETURN
IF(
CalendarDate[LocalDateTime] >= EDATE(CurrentLocalDate, -12) && CalendarDate[LocalDateTime] <= CurrentLocalDate,
"Last 12 Months",
"Not Last 12 Months"
)
Step 4: Ensure Proper Relationships
Make sure your data model has the following relationships set correctly:
Active relationship between CalendarDate[Date] and Sales[Date].
Inactive relationship between CalendarDate[TimeZone] and Sales[TimeZone].
Step 5: Use the Flag in Reports
Use the Last12MonthsFlag in your visual filters to show or hide data based on the flag:
Add the Last12MonthsFlag to your visuals as a filter.
Set the filter to display only data where Last12MonthsFlag equals "Last 12 Months".
To achieve the required calculations using the inactive relationship in DAX, you need to activate the relationship within the calculation. Here’s how you can properly set up the calculation for the CurrentDay and subsequently use it to flag the last 12 months based on the country selected.
Step 1: Correct Calculation of CurrentDay
The DAX expression you provided should work with USERELATIONSHIP, but you need to ensure it’s properly structured. Also, since the VALUES function returns a table, you might want to use SELECTEDVALUE or MIN/MAX if you expect a single value.
CurrentDay =
CALCULATE(
SELECTEDVALUE(CalendarDate[LocalDateTime]),
USERELATIONSHIP(Sales[TimeZone], CalendarDate[TimeZone])
)
Step 2: Flagging Last 12 Months
Next, you need to create the flag for the last 12 months in the CalendarDate table based on the country selected. Ensure that you are correctly using the CurrentDay calculated column.
Adjusting the Date Column for the Last 12 Months
You’ll need a calculated column or measure to determine if the date falls within the last 12 months from the CurrentDay.
Calculated Column for Last 12 Months Flag
First, ensure your CurrentDay calculation works and reflects the correct local date. Then, use this value to create a flag for the last 12 months.
Step 3: Create the Last 12 Months Flag in CalendarDate
Create a calculated column in the CalendarDate table to flag dates within the last 12 months based on the local date:
Last12MonthsFlag =
VAR CurrentLocalDate =
CALCULATE(
MAX(Sales[LocalTime]),
USERELATIONSHIP(Sales[TimeZone], CalendarDate[TimeZone])
)
RETURN
IF(
CalendarDate[LocalDateTime] >= EDATE(CurrentLocalDate, -12) && CalendarDate[LocalDateTime] <= CurrentLocalDate,
"Last 12 Months",
"Not Last 12 Months"
)
Step 4: Ensure Proper Relationships
Make sure your data model has the following relationships set correctly:
Active relationship between CalendarDate[Date] and Sales[Date].
Inactive relationship between CalendarDate[TimeZone] and Sales[TimeZone].
Step 5: Use the Flag in Reports
Use the Last12MonthsFlag in your visual filters to show or hide data based on the flag:
Add the Last12MonthsFlag to your visuals as a filter.
Set the filter to display only data where Last12MonthsFlag equals "Last 12 Months".
User | Count |
---|---|
20 | |
18 | |
15 | |
11 | |
7 |
User | Count |
---|---|
28 | |
27 | |
13 | |
12 | |
12 |