Power BI Result Discrepancy: Incorrect Value for Single Date Filter

[manoj_0911]

Hello Power BI Community,

I'm facing an issue with the data I am working with in Power BI. When I apply a date filter (TXN_DATE = '2024-12-02') on a card visual for `ACCEPTED` from the `AG2_AGENT_SUBHOUR` table, I get a result of **63**, whereas in the database query it returns **2** for the same date.

What I've tried so far:
1. I re-created all relationships between the `AG2_AGENT_SUBHOUR` and dimension tables (DATE_TIME, PERSONS, QUEUES, etc.) in Power BI exactly as they appear in the SQL query.
2. I'm using the `DATE_TIME` table for the date slicer.
3. I've tried debugging the formula with `CALCULATE`, and it works for specific dates using DAX like:
```DAX
DebugAccepted = CALCULATE(SUM(AG2_AGENT_SUBHOUR[ACCEPTED]), DATE_TIME[TXN_DATE] = DATE(2024, 12, 2))

[Anonymous · Accepted solution]

Hi, @manoj_0911 

I am glad to help you.

You need to ensure that the data in Power BI is up to date. Sometimes the data in Power BI may not be refreshed and cause inconsistent results.

You need to check if there are other filters or slicers in Power BI that are affecting the results. Although the DAX formula you provided appears to be correct, other filters may be affecting the ACCEPTED value.

You need to double-check the relationships in Power BI. Even if you recreated them, there may be subtle differences. Make sure that the relationship is set up correctly and is active.

You can verify that the data types of TXN_DATE in the AG2_AGENT_SUBHOUR and DATE_TIME tables are consistent. Inconsistent data types can sometimes lead to unexpected results.

You need to ensure that the aggregation settings in Power BI are correct. The card visual should correctly aggregate ACCEPTED values.

Or you can use the following DAX for debugging:

DebugTable = 
SUMMARIZE(
    AG2_AGENT_SUBHOUR,
    DATE_TIME[TXN_DATE],
    "Accepted", SUM(AG2_AGENT_SUBHOUR[ACCEPTED])
)

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hi, @manoj_0911 

I am glad to help you.

You need to ensure that the data in Power BI is up to date. Sometimes the data in Power BI may not be refreshed and cause inconsistent results.

You need to check if there are other filters or slicers in Power BI that are affecting the results. Although the DAX formula you provided appears to be correct, other filters may be affecting the ACCEPTED value.

You need to double-check the relationships in Power BI. Even if you recreated them, there may be subtle differences. Make sure that the relationship is set up correctly and is active.

You can verify that the data types of TXN_DATE in the AG2_AGENT_SUBHOUR and DATE_TIME tables are consistent. Inconsistent data types can sometimes lead to unexpected results.

You need to ensure that the aggregation settings in Power BI are correct. The card visual should correctly aggregate ACCEPTED values.

Or you can use the following DAX for debugging:

DebugTable = 
SUMMARIZE(
    AG2_AGENT_SUBHOUR,
    DATE_TIME[TXN_DATE],
    "Accepted", SUM(AG2_AGENT_SUBHOUR[ACCEPTED])
)

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[manoj_0911 · Accepted solution]

"While doing data validation between db and pbi report,  for querying like this WHERE dt.TXN_DATE >= '2024-12-12 00:00:00' AND dt.TXN_DATE < '2024-12-13 00:00:00'; I was checking like this WHERE TXN_DATE = '2024-12-12'; Data is now Matching"