Time intelligence not working but manual filtering does?

[SevsBo]

I have a simple Table1 connected to DateTable, where I am trying to calculate the count of Items in current month where ItemName from Table2 = Banana.

If I create a Card and drag in Item, using Count, and add filters based on ItemName = Banana and DateTable is in current month, I get expected results. But so far I have failed to do the same in a measure.

I tried two approaches:

Approach1 =

CALCULATE(

    COUNTA('Table1'[Item]),

    DATESMTD(DatesTable[Date]),

    'Table2'[ItemName] = "Banana")

And:

Approach2 = 

TOTALYTD(
    COUNTA('Table1'[Item]),

    DATESMTD(DatesTable[Date]),

    'Table2'[ItemName] = "Banana")

Both return Blank results in a Card visual. What am I doing wrong?

[SevsBo · Accepted solution]

No solution found but flagging this as solution to close thread.

[v-pnaroju-msft]

Hi SevsBo,

Please confirm if your issue has been resolved. If yes, you may share your workaround and mark it as the solution so that other users can benefit. This will help community members facing similar problems to find solutions faster.

If we do not hear from you, we will go ahead and close this thread. For any further assistance in the future, kindly reach out through the Microsoft Fabric Community Forum by creating a new thread. We will be happy to assist you.

Thank you.

[v-pnaroju-msft]

Hi SevsBo,

Kindly confirm if the issue has been resolved. If so, you may share your workaround and mark it as a solution, enabling other users to benefit from it. This will assist fellow community members facing similar challenges to resolve them more quickly.

In the absence of a response, we shall proceed to close this thread. Should you require any further assistance in the future, we encourage you to reach out through the Microsoft Fabric Community Forum by creating a new thread. We will be glad to assist you.

Thank you.

[v-pnaroju-msft]

Hi SevsBo,

We are following up to enquire whether your query has been resolved. If the issue has been resolved, we would greatly appreciate it if you could share the solution with the community, as it may assist others facing similar challenges.

However, if the issue remains unresolved and you have any further queries related to it, please feel free to contact the Microsoft Fabric community.

Kindly provide sample data that fully illustrates your issue or question, in a usable format (not as a screenshot). Please ensure that the data does not contain any sensitive information or unrelated content. Additionally, please indicate the expected outcome based on the sample data provided.

Thank you.

[v-pnaroju-msft]

Hi SevsBo,

We are following up to enquire whether your query has been resolved. If the issue has been resolved, we would greatly appreciate it if you could share the solution with the community, as it may assist others facing similar challenges.

However, if the issue remains unresolved and you have any further queries related to it, please feel free to contact the Microsoft Fabric community.

Kindly provide sample data that fully illustrates your issue or question, in a usable format (not as a screenshot). Please ensure that the data does not contain any sensitive information or unrelated content. Additionally, please indicate the expected outcome based on the sample data provided.

Thank you.

[v-pnaroju-msft]

Hi SevsBo,

Thank you for your response. We apologise for the inconvenience caused.
Kindly provide sample data that fully illustrates your issue or question, in a usable format (not as a screenshot). Please ensure that the data does not contain any sensitive information or unrelated content. Additionally, please indicate the expected outcome based on the sample data provided.

Thank you.

[v-pnaroju-msft]

Hi SevsBo,

We wanted to check in regarding your query, as we have not heard back from you. If you have resolved the issue, sharing the solution with the community would be greatly appreciated and could help others encountering similar challenges.

If you found our response useful, kindly mark it as the accepted solution and provide kudos to guide other members.

Thank you.

[SevsBo]

I don't think we've found a solution so I can't really accept a solution. But happy to close this.

[v-pnaroju-msft]

Hi SevsBo,

We have not received a response from you regarding the query and were following up to check if you have found a resolution. If you have identified a solution, we kindly request you to share it with the community, as it may be helpful to others facing a similar issue.

If you find the response helpful, please mark it as the accepted solution and provide kudos, as this will help other members with similar queries.

Thank you.

[v-pnaroju-msft]

Thankyou, @bhanu_gautam, @johnt75, for your response.

Hi @SevsBo,

Thank you for your follow-up.

As per my understanding, the time intelligence functions (TOTALMTD or DATESMTD) rely heavily on the active filter context, particularly from your DateTable. When used independently, these functions do not inherently filter for the "current month" unless the time range is explicitly specified.

These functions operate based on the maximum visible date within the current filter context. If your visual or page does not explicitly restrict the dates to the current month, DATESMTD may refer to the last date present in the dataset, which could be a future date, resulting in the function returning blank values.

This explains why your manually filtered card worked, whereas the measure did not, as it lacked an explicit date boundary.

Please find attached the screenshot and PBIX file, which may assist in resolving the issue:

If you find our response helpful, kindly mark it as the accepted solution and provide kudos. This will help other community members facing similar queries.

Thank you.

[johnt75]

DATESMTD doesn't know that you want it to start from today without an explicit filter, so it will start from the last date in your calendar table.

Try

Num items =
CALCULATE (
    COUNTROWS ( 'Table1' ),
    DATESBETWEEN ( DatesTable[Date], EOMONTH ( TODAY (), -1 ) + 1, TODAY () ),
    'Table2'[ItemName] = "Banana"
)

[SevsBo]

That's a similar approach as what I've just tried:

Approach3 =

var startdate = DATE(YEAR(TODAY()), MONTH(TODAY()), 1)

var enddate = DATE(YEAR(TODAY()), MONTH(TODAY()), 31)

RETURN

CALCULATE(

    COUNTA(

        'Table1'[Item]),

        'Table2'[ItemName] = "Banana",

        DatesTable[Date] >= startdate && DatesTable[Date] <= enddate

)


Now I'm trying to build a comparison with last year, will see how that goes.

Out of curiosity, how does TOTALMTD not implicitly filter for current month? Surely that's implied in the name of the funciton. Month-to-date?

[bhanu_gautam]

@SevsBo Try using

DAX
Approach1 =
CALCULATE(
COUNTA('Table1'[Item]),
DATESMTD(DatesTable[Date]),
FILTER('Table2', 'Table2'[ItemName] = "Banana")
)

DAX
Approach2 =
CALCULATE(
TOTALYTD(
COUNTA('Table1'[Item]),
DatesTable[Date]
),
FILTER('Table2', 'Table2'[ItemName] = "Banana")
)

[SevsBo]

Sadly neither of those work. It's interesting the first option seems to work if I add another measure for SAMEPERIODLASTYEAR, which returns a value but that value bears no relation to reality.

It's strange as I'm using the exact same fields as when I'm manually filtering it.