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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
jonclay
Helper IV
Helper IV

Problem with using DATE in DAX within Power BI desktop

Hi everyone

I have a very strange problem relating to dates within Power BI. Hopefully I can explain this well....

I'm using the following DAX code to output some financial values into a Power BI matrix. I've highlighted the second row as I believe this is the key to the issue:

Total_OLD_Pl_DC1 = IF (TR_1_DC1_Burs[pledges.sic_pledgedate] >= DATE ( 1900, 9, 1)
    && TR_1_DC1_Burs[pledges.sic_pledgedate] <= DATE( 2020, 8, 31)
&& TR_1_DC1_Burs[statuscodename] ="Paid"
&& TR_1_DC1_Burs[si_transactiondateofpayment] >= DATE ( 2020, 9, 1 )
&& TR_1_DC1_Burs[si_transactiondateofpayment] <= DATE ( 2021, 8, 31 ),
( TR_1_DC1_Burs[sic_dc1totalincgiftaid] ))

We have a row where the pledges.sic_pledgedate is 01/09/2020 (or 2020, 9, 1) and yet the financial amount attributed to this is appearing in the output. Surely, the second line of the DAX code shown above would ensure that it only looks at dates prior to 01/09/2020.

Is this something to do with how Power BI (or the DATE function within it) looks at dates and, if so, how can I remedy this?

Many thanks
Jon
2 ACCEPTED SOLUTIONS
lbendlin
Super User
Super User

Power BI Service runs on UTC.  You probably have transactions from different timezones.

View solution in original post

Anonymous
Not applicable

HI @jonclay,

What type of DateTime values are you used? Common DateTime values or DateTime with local timezones?

AFAIK, power bi service will convert your DateTime value with UTC format. If you are working with the second scenario, they may not get the same result when these expressions are calculated on different sides.

In addition, please also check the daylight saving scenario and it can also affect DateTime values:

Chris Webb's BI Blog: Daylight Saving Time And Time Zones In M Chris Webb's BI Blog (crossjoin.co.uk...

Regards,

Xiaoxin Sheng

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

HI @jonclay,

What type of DateTime values are you used? Common DateTime values or DateTime with local timezones?

AFAIK, power bi service will convert your DateTime value with UTC format. If you are working with the second scenario, they may not get the same result when these expressions are calculated on different sides.

In addition, please also check the daylight saving scenario and it can also affect DateTime values:

Chris Webb's BI Blog: Daylight Saving Time And Time Zones In M Chris Webb's BI Blog (crossjoin.co.uk...

Regards,

Xiaoxin Sheng

lbendlin
Super User
Super User

Power BI Service runs on UTC.  You probably have transactions from different timezones.

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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