Unusual Data Mismatch

[GanesaMoorthyGM]

Hii Guys,

So Today I was working in Sales and Target report and Unusual mismatch occurs, let me explain
So i have Cluster Dim which models with fact sales and Cluster Target

Cluster DIM has
Cluster ID
Cluster

here Cluster is mapped with fact_sales Product Cluster and Cluster Target Product Cluster

the mismatch is for Gold_Coins in Oman alone the value mismatch occurs in power bi table visual it shows 0.25 but the actual in excel is 0.39

I'll be using Cluster Dim only for sales and Target data comparison and the relationship is also fine i guess but i dont know why this occurs kindly help me fixing this.
Thanks in advance

[GanesaMoorthyGM]

So guys I was still not able to sort this out,
I'll add additional detail and screenshot 0.25 it doesnt include Target of June,July,August
whereas 0.39 includes upto August from april but 0.25 is the target value of April and May alone


But the measure that i used is same for these 2 visuals

Oman YTD Target =

VAR LastSalesDate = CALCULATE(

    MAX('Fact_Sales_Master'[DateOnly]),

    FILTER(

        'Fact_Sales_Master',

        'Fact_Sales_Master'[Country] = "Oman"

    )

)

VAR CurrentYear = YEAR(LastSalesDate)

VAR CurrentMonth = MONTH(LastSalesDate)

-- Determine the fiscal year start based on last available sales date

VAR FiscalYearStartYear = IF(CurrentMonth >= 4, CurrentYear, CurrentYear - 1)

RETURN

COALESCE(

    CALCULATE(

        SUM('Cluster Target'[Target]),

        FILTER(

            'Cluster Target',

            'Cluster Target'[Country] = "Oman" &&

            VAR MonthNumber = MONTH(DATEVALUE("01 " & 'Cluster Target'[Month] & " 2000"))

            VAR MonthYear = IF(MonthNumber >= 4, FiscalYearStartYear, FiscalYearStartYear + 1)

            VAR TargetDate = DATE(MonthYear, MonthNumber, 1)

            RETURN

                TargetDate >= DATE(FiscalYearStartYear, 4, 1) &&

                TargetDate <= DATE(CurrentYear, CurrentMonth, 1)

        )

    ),

    0

)

this is my measure

[v-achippa · Accepted solution]

Hi @GanesaMoorthyGM,

Is you issue resolved? If not please follow the below approach.

Here the mismatch happens because your current measure calculates the last sales date in a cluster filtered context. So in the cluster dim visual this cuts off early, so you only see 0.25.
To fix it, please use the below measure:

Oman YTD Target =
VAR LastSalesDate = CALCULATE(
MAX('Fact_Sales_Master'[DateOnly]),
ALL('Fact_Sales_Master'),
'Fact_Sales_Master'[Country] = "Oman"
)

This way both visuals will consistently return 0.39 for Gold_Coins, matching your excel result.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @GanesaMoorthyGM,

We wanted to kindly follow up to check if the solution I have provided for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @GanesaMoorthyGM,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution I have provided for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa

[danextian]

This could be a case of value filter behaviour - Work with value filter behavior 

[Shahid12523]

The mismatch (0.25 in Power BI vs 0.39 in Excel for Gold_Coins in Oman) is likely due to one of these:
- Filter context: Power BI might be filtering Oman differently.
- Stale data: Excel was updated but Power BI wasn’t refreshed.
- Mapping issue: Gold_Coins might not be mapped correctly in Cluster DIM.
- DAX logic: Your measure might exclude some rows unintentionally.
Start by checking filters, refreshing data, and validating mappings.

[GanesaMoorthyGM]

Thanks for uour quick response @jaineshp ,

I already tried this

1. Double-check if Gold_Coins exists with duplicates or small spelling/space differences across DIM and Target.

2. Make sure the relationship between Cluster DIM → Target is 1-to-many, single direction.
   But still not fixed

[Natalie_iTalent]

Hello @GanesaMoorthyGM, the comment you are responding to has been removed. 

Best,

Natalie H.

Community Manager 

[v-achippa]

Hi @GanesaMoorthyGM,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @jaineshp, @Shahid12523 and @danextian for the prompt response. 

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user's for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa