Search code examples
powerbidaxdata-analysispowerbi-desktop

What am I missing for time intelligence to work?


This measure returns 5, which is correct

Count = COUNTROWS('Fact')

This measure however returns zero results

Count MTD = CALCULATE(COUNTROWS('Fact'), DATESMTD('Date'[Date]))

What am I missing? Do I need additional columns in my database model? Is the formatting incorrect? Why is it returning zero results?

If the date range in Dim table stops at the end of June the MTD measure works correctly, however if the date range goes beyond June and into July, it stops working. Why is this happening and what am I missing?

Blank result and zero filtering PowerBI


Solution

  • There are a few misconceptions going on here. Firstly, your date table should have a full calendar year for the values that exist in your fact table. In your case, you need a calendar table that goes from 01/01/2023 - 31/12/2023. Secondly, you must mark this as a date table as follows:

    enter image description here

    Now the actual logic of DATESMTD. If you write your measure and place it in a card, then the answer will be blank. This is because of the filter context. If you place your measure in a table, it will look like this:

    enter image description here

    The reason it works in table is because the evaluation context is being provided by each row and so you get the right result. When you place the measure in a card, the last month in context is December and so the answer is blank. If you have rows in your fact table for December then they will show in the card.

    When you say DATESMTD to DAX, it needs to know the month you're talking about. If no month is supplied, it will take the last visible one which in this case is December and so correctly returns blank.