Forum Discussion

LLJ1221's avatar
LLJ1221
Icon for Helper I rankHelper I
2 years ago

Cumulative sum by year/month selected

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)

YearMonthNumber of referenceCumulative
01/20213232
02/2021 97117
03/2021277394
04/2021 8541244
05/2021 3351478
06/2021 5792001
07/2021 4682384
08/20213152679
09/20212462893
10/20212023072
11/20211553197
12/20212193261

 

Thanks in advance for your help 🙂

General Comment
Need Help

4 Replies

  • az38's avatar
    az38
    Icon for Community Champion rankCommunity Champion
    2 years ago

    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

  • LLJ1221's avatar
    LLJ1221
    Icon for Helper I rankHelper I
    2 years ago

    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

     

  • az38's avatar
    az38
    Icon for Community Champion rankCommunity Champion
    2 years ago

    LLJ1221 

     

    try this

     

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