Forum Discussion
Impactful IndividualNeedle in a Haystack?
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
Impactful Individualone 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")
Impactful Individualwhen 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.