Forum Discussion

db042190's avatar
db042190
Icon for Impactful Individual rankImpactful Individual
1 month ago
Solved

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 refr...
Need Help
  • v-csrikanth's avatar
    v-csrikanth
    1 month ago

    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.

    Thanks,
    C Srikanth
    Community Support Team

v-csrikanth's avatar
v-csrikanth
Icon for Community Support rankCommunity Support
1 month ago

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 Team