Wrong annual cumulative total

[Syndicate_Admin]

Hello, a favor, I am using the DATESYDT function but in a couple of tables it is not recognizing the annual cumulative sum.

What could be registering wrong?

Thank you.

[Syndicate_Admin]

Hello, well adding freehand it is clearly seen that it is not right, neither the total nor the accumulated.

[v-rzhou-msft · Accepted solution]

Hi @Syndicate_Admin ,

I create a easy sample to have a test and explain more to you.

Autonomias:

Calendar:

Calendar =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2021, 01, 01 ), DATE ( 2021, 03, 31 ) ),
    "Month", MONTH ( [Date] ),
    "MonthName", FORMAT ( [Date], "MMM" )
)

Measures:

Locales = DISTINCTCOUNT(Autonomias[Locales])

Locales acumulado = CALCULATE([Locales],DATESYTD('Calendar'[Date]))

Result:

[Locales] measure will distinctcount locales for each Month, and the total is distinctcount for all month = 4.

You create [Locales acumulado] by DATESTYD, so Jan will distinctcount locals for Jan (A C A) =2, Feb will distinctcount locals for Jan and Feb (A C A, A B C) = 3, Total will distinctcount locals for whole year = 4. There is only distinctcount and no sum. So the result is correct.

If you want to calculate acumulate distinct locals , create a virtual table and try sumx function.

New_Locales acumulado =
VAR _SUMMARIZE =
    SUMMARIZE (
        ALL ( 'Calendar' ),
        'Calendar'[MonthName],
        'Calendar'[Month],
        "Locals", [Locales]
    )
RETURN
    SUMX ( FILTER ( _SUMMARIZE, [Month] <= MAX ( 'Calendar'[Month] ) ), [Locals] )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[VijayP]

@Syndicate_Admin 

Whatever Scenario shown in above table is absolutely fine. The Total Aggregation value will always be equal to the final cumulative value!