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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
deb_power123
Helper V
Helper V

DAX formula to show the Attendance date greater than equal to System date using -UTCNOW() or NOW()?

Hi All,

 

I have a Student table with 5 columns.

 

I have a typical issue where I need to show the datetime for student AttendanceDate for region Armonk and region London . I want any user from USA should only see the records of today's attendance date but  for USA [Armonk]it is showing yesterday's date entries [marked in red in the below screenshot]and but for any user in Europe are able see the attendance of today's date .

 

Could anyone suggest any DAX to handle this? I tried the below DAX but it didnt show any record for USA while it showed correct record for Europe region.I am located in Europe region and I published the report from my local machine? Does that make a difference?

 

DAX calculated column  formula :-

SYSTEM_TIME_REF = NOW()

FLAG = IF(student[attendance_date] >= student[SYSTEM_TIME_REF],0,1)

 

Input data source :

AttendanceDateLocationStudentIDNameRegion
20.08.2021 11:05AMGate1101TomLondon
20.08.2021 10.03.22Gate1102HaryLondon
20.08.2021 10.09.21Gate2103RohanArmonk
20.08.2021 09.03.18Gate2105MohanArmonk
19.08.2021 10.08.22Gate2109SamArmonk
19.08.2021 10.03.12Gate2110SallyArmonk

 

Expected Output:

attendance.JPG

 

 

Whenever the Attendance_Date >= systemtime date then it should set the flag as 0 else 1.Therefore we can filter and show records of the present date based on system date but in case of USA it is showing one day older than the system date while in Europe region it is showing correct records.How to fix this issue , please suggest a DAX?

 

Can someone please suggest any DAX changes or any DAX forumla to handle this in a better way and to rectify this issue?

 

Kind regards

Sameer

1 REPLY 1
lbendlin
Super User
Super User

Have you see this article yet?  Solving DAX Time Zone Issue in Power BI - RADACAD

 

You will also want to make sure that your [AttendanceDate]  column is of DateTimeZone type.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors