Forum Discussion

GanesaMoorthyGM's avatar
GanesaMoorthyGM
Icon for Helper II rankHelper II
1 year ago
Solved

Unusual Data Mismatch

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 C...
Need Help
Show and Tell
Tips and Tricks
  • v-achippa's avatar
    v-achippa
    1 year ago

    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

GanesaMoorthyGM's avatar
GanesaMoorthyGM
Icon for Helper II rankHelper II
1 year ago

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's avatar
    v-achippa
    Icon for Community Support rankCommunity Support
    1 year ago

    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's avatar
      v-achippa
      Icon for Community Support rankCommunity Support
      11 months ago

      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's avatar
        v-achippa
        Icon for Community Support rankCommunity Support
        11 months ago

        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