Forum Discussion

BB76's avatar
BB76
Helper I
3 years ago
Solved

DAX measure does not sum value correctly

Dear All,

 

i have the following measures defined:

 

Measure: NearestDate =
VAR SelectedDate = SELECTEDVALUE(DatesSelectable[Date])
RETURN
    MINX(
        FILTER(Non_FIMI_Data, Non_FIMI_Data[Entity Betis Number] = [SelectedBETIS]),
        IF(
            Non_FIMI_Data[Date] >= SelectedDate,
            ABS(DATEDIFF(Non_FIMI_Data[Date], SelectedDate, DAY)),
            BLANK()
        )
    ) + SelectedDate
 
Measure: TOTAL_FACT_NCAMOUNT =
CALCULATE(
    SUM(Non_FIMI_Data[Amount in EUR]),
    FILTER (
    Non_FIMI_Data,
    Non_FIMI_Data[Date] = [NearestDate] &&
    Non_FIMI_Data[Entity Betis Number] = [SelectedBETIS]
    )    
)
 
When applying this to a report the measure [TOTAL_FACT_NCAMOUNT] seems to sum up every entry in the table (corresponding to the correct ID (here 5474)) which is on or after the [NearestDate].

 

 

Resulting Data Excerpt 

DateTOTAL_FACT_NCAMOUNTNearestDateSelectedBETIS
... ......
29.03.2023 00:00:00 31.03.2023 00:00:005474
30.03.2023 00:00:00 31.03.2023 00:00:005474
02.04.2023 00:00:001002.04.2023 00:00:005474
03.04.2023 00:00:001003.04.2023 00:00:005474
04.04.2023 00:00:001004.04.2023 00:00:005474
10.04.2023 00:00:001010.04.2023 00:00:005474
11.04.2023 00:00:001011.04.2023 00:00:005474
12.04.2023 00:00:001012.04.2023 00:00:005474
13.04.2023 00:00:001013.04.2023 00:00:005474
16.04.2023 00:00:001016.04.2023 00:00:005474
17.04.2023 00:00:001017.04.2023 00:00:005474
18.04.2023 00:00:001018.04.2023 00:00:005474

 

 

I am lost, how to tackle the problem. Goal is to get only sum of VALUES on NearestDate and SelectedBetis in Table "Non_FIMI_Data"

Thanks for any thought, ideas.

Many thanks
BB

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    HI BB76,

    It seems like a common measure calculate issue when it works with multiple level of aggregations.

    For this scenario, I'd like to suggest you refer to the following blog to add variable with SUMMARIZE function to handle the first level calculations. Then you can use iterator functions to summary previous step calculation results.

    Measure Totals, The Final Word 

    Regards,

    Xiaoxin Sheng

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    HI BB76,

    It seems like a common measure calculate issue when it works with multiple level of aggregations.

    For this scenario, I'd like to suggest you refer to the following blog to add variable with SUMMARIZE function to handle the first level calculations. Then you can use iterator functions to summary previous step calculation results.

    Measure Totals, The Final Word 

    Regards,

    Xiaoxin Sheng

  • BB76's avatar
    BB76
    Helper I
    3 years ago

    peferct thanks giving me a hint. Solution is to introduce [NeareasDate] as variable:

    TOTAL_FACT_NCAMOUNT =
    Var nearestDate = [NearestDate]
    Return
    CALCULATE(
        SUM(Non_FIMI_Data[Amount in EUR]),
        FILTER (
        Non_FIMI_Data,
        Non_FIMI_Data[Date] = nearestDate &&
        Non_FIMI_Data[Entity Betis Number] = [SelectedBETIS]
        )    
    )