Needle 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