Cumulative sum by year/month selected

[LLJ1221]

Hello,

I have a problem with calculating an cumulative by year/month.

I have a calendar dimension table with column Year/Month  and i have a table fact with Number of reference.

I do this formula Dax for caculating the running total but the formula return a bad resultat. 

Cumulative = CALCULATE (
    [Number of reference],
    FILTER (
        ALL (calendar_table),
        calendar_table[Date] <= MAX (calendar_table[Date])
            && calendar_table[Year] = MAX(calendar_table[Year])
    )
)

For example in 02/2021 the result will be 129 not 117 (32+97)

YearMonth	Number of reference	Cumulative
01/2021	32	32
02/2021	97	117
03/2021	277	394
04/2021	854	1244
05/2021	335	1478
06/2021	579	2001
07/2021	468	2384
08/2021	315	2679
09/2021	246	2893
10/2021	202	3072
11/2021	155	3197
12/2021	219	3261

Thanks in advance for your help 🙂

[az38]

@LLJ1221 

try this

Cumulative = 
CALCULATE (
    SUMX('CAGC_MF_INVENTAIRE_STOCK_REP', [Number of reference]), DATESYTD(calendar_table[Date])
   )

[LLJ1221]

Hi @az38,

The "Number of reference" column corresponds to a DAX formula which counts a column separately.

The DAX measurement is as follows:

 Number of reference = DISTINCTCOUNT(CAGC_MF_INVENTAIRE_STOCK_REP[Reference/Division/Store])

The data in this column looks like this:
01100-B001-ARS05

01100-B001-ARS05

01100-B001-ARS05

5001-B142-WES05

5001-B142-WES05

9875 - B180-AM56

9875 - B180-AM56

9875 - B180-AM56

9875 - B180-AM56

9875 - B180-AM56

[Ahmedx]

pls try this

[az38]

Hi @LLJ1221 

How does look like your [Number of reference] measure?

I highly suspect there is some distinct and from this perspective the behaviour above is totally expected