cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jonclay
Helper I
Helper I

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

v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

2 REPLIES 2
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

lbendlin
Super User
Super User

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

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.