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

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
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors