Time intelligence not working but manual filtering does?

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")
)

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"
)

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.

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.

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.

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.

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.

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.

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.

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.