Forum Discussion
Needle in a Haystack?
Hi, i inherited an import pi report with an auxiliary like table called Measure_Metrics. That table has over 100 calculations in it like Sales-MTD. When i hover over the table, rather than a refresh date i see "Invalid Date". There are no records in it in the desktop environment. Would finding what causes the "invalid date" error in a table with over 100 calcs be like finding a needle in a haystack? Or is there a way to better pinpoint which calc(s) is/are causing the problem? Our eyes were drawn to the issue when a number of kpis started showing double dashes rather than actual values.
Hi db042190
The empty Measure_Metrics table is not the issue. It's an Enter Data table used as a home for measures, so having zero rows is expected. Measures are evaluated at query time and don't create rows there, so the "Invalid Date" text isn't evidence that one of them is failing.
The key issue is your DimDate[MTD] column:
MTD = IF ( DAY ( NOW() - 6/24 ) <= DimDate[DayNumberOfMonth], "No", "Yes" )On the 1st of the month, DAY() returns 1, making 1 <= DayNumberOfMonth true for every row. MTD becomes "No" everywhere, and measures filtering on MTD = "Yes" return blank — your --.
That's also why the workspace shows no refresh error: the model refreshes fine, the column just resolves to a value that filters every date out.
Neither of your changes addressed the cause. -6/24 and -(6/24) are identical. Removing -6/24 only appeared to help because by the 2nd the reference day was no longer 1 — the original formula would have recovered on its own. Expect the same condition on 1 October.
For djurecic's question: the arithmetic is valid DAX rather than an Excel-ism — NOW()+3.5 is the reference's own example.
If the intention is to calculate consistently from UTC:
MTD = VAR RefDay = DAY ( UTCNOW () - 6/24 ) RETURN IF ( RefDay < DimDate[DayNumberOfMonth], "No", "Yes" )Use < only if today should be included in MTD; keep <= if today is deliberately excluded.
One additional point: NOW() returns UTC in the Power BI Service while Desktop uses local time, so Desktop and Service refreshes can differ around the date boundary. Calculated columns also only recalculate on refresh, so the flag stays wrong until the next one — worth checking before you publish.
You can verify this quickly in DAX query view → Quick queries → Evaluate by checking the distinct values of MTD.
- https://learn.microsoft.com/dax/now-function-dax
- https://learn.microsoft.com/dax/utcnow-function-dax
Thanks,
C Srikanth
Community Support Team
11 Replies
Hi @db042190,
Partly, but the numbers point to Central, not Eastern. EST is UTC−5 and EDT is UTC−4; UTC−6 is CST (or MDT in summer).
On UTCNOW(), I wouldn't assume the author forgot. In the Service, NOW() already returns UTC, so NOW() - 6/24 genuinely means UTC minus six hours where the refresh actually runs. UTCNOW() is still preferable because it makes Desktop and the Service agree, not because the original might be wrong in production.
But the timezone isn't what breaks the KPIs. Every timezone has a 1st of the month, so whichever offset you use, DAY() eventually returns 1 and 1 <= DimDate[DayNumberOfMonth] is true for every row. The offset only shifts when that starts during the day it can't prevent it. Switching to UTCNOW() or changing -6 to -5 moves the boundary by an hour or two; neither stops MTD going all-"No", and the same thing will happen on 1 October.
Also worth confirming before you change the number: -6/24 may not be a timezone conversion at all, but a six-hour grace period after an overnight load.
So treat UTCNOW() as a consistency improvement the <= comparison is what actually needs deciding.
Thanks,
C Srikanth
Community Support TeamHi db042190
The empty Measure_Metrics table is not the issue. It's an Enter Data table used as a home for measures, so having zero rows is expected. Measures are evaluated at query time and don't create rows there, so the "Invalid Date" text isn't evidence that one of them is failing.
The key issue is your DimDate[MTD] column:
MTD = IF ( DAY ( NOW() - 6/24 ) <= DimDate[DayNumberOfMonth], "No", "Yes" )On the 1st of the month, DAY() returns 1, making 1 <= DayNumberOfMonth true for every row. MTD becomes "No" everywhere, and measures filtering on MTD = "Yes" return blank — your --.
That's also why the workspace shows no refresh error: the model refreshes fine, the column just resolves to a value that filters every date out.
Neither of your changes addressed the cause. -6/24 and -(6/24) are identical. Removing -6/24 only appeared to help because by the 2nd the reference day was no longer 1 — the original formula would have recovered on its own. Expect the same condition on 1 October.
For djurecic's question: the arithmetic is valid DAX rather than an Excel-ism — NOW()+3.5 is the reference's own example.
If the intention is to calculate consistently from UTC:
MTD = VAR RefDay = DAY ( UTCNOW () - 6/24 ) RETURN IF ( RefDay < DimDate[DayNumberOfMonth], "No", "Yes" )Use < only if today should be included in MTD; keep <= if today is deliberately excluded.
One additional point: NOW() returns UTC in the Power BI Service while Desktop uses local time, so Desktop and Service refreshes can differ around the date boundary. Calculated columns also only recalculate on refresh, so the flag stays wrong until the next one — worth checking before you publish.
You can verify this quickly in DAX query view → Quick queries → Evaluate by checking the distinct values of MTD.
- https://learn.microsoft.com/dax/now-function-dax
- https://learn.microsoft.com/dax/utcnow-function-dax
Thanks,
C Srikanth
Community Support Teamthx djurecic. i think the original author meant to subtract 6 hrs from now() to see if that overlapped into yesterday. cant reach him at the moment. According to AI, because of the internal format of now() , you have to do it liuke that.
hi djurecic, after reading C Srikanth's post, im starting to think the author meant to use UTCNOW and change it to EST by subtracting 6/24...so the report would work in any cloud region the same way it would run on anyone's desktop.
i should have pointed out that in the ws we see no error symbol suggesting the daily refresh failed. and i'm trying a refresh on the desktop and watching the refresh list of tables to see if i'll have an opportunity to see the error and more detail related to the error. So far i see Schema synchronized which i think means the tables it depends on are refreshed. Also, when i click edit query i see the source shown below which at the moment means nothing to me. There are 2 applied steps called change type and remove columns.
the refresh on the desktop finished without giving me an opportunity to see a failed status on this table.
= Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i44FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t])
one other thing im noticing is that this calc in dimdate is always returning NO. And this report depends on MTD flags in a variety of places including where month is designated as current month type CM.
MTD = IF(DAY(now()-6/24) <= (DimDate[DayNumberOfMonth]), "No","Yes")
when i replaced -6/24 with -(6/24) the issue persisted. When i removed -6/24 altogether the report started working. I still see an empty auxiliary table and i still see "invalid date" when i hover over its name. I' guessing right now that "invalid date" applies to one calc that maybe doesnt matter? And that 0 rows showing for the auxiliary on the desktop is ok , maybe?
Can the community clarify why my change made a difference and why the weird behavior surrounding the auxiliary table continues. As far as i can tell, calcs in the auxiliary source the kpis that were broken but that suddenly started working on the desktop. im waiting to publish until i see some of the responses here. According to my peers this has been working for weeks. And suddenly broke yesterday.
