Problem with using DATE in DAX within Power BI desktop

[jonclay]

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

[lbendlin · Accepted solution]

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

[Anonymous · Accepted solution]

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 · Accepted solution]

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