Accumulated sum that resets at the turn of the year

[Anonymous]

Hi,

I'm trying to create a measure that makes a cumulative sum per month (count number of rows in my table), but the measure I created is having problems with the same months for different years. For example, for the month of May, it is counting the number of rows accumulated until May 2022 + the number of rows of May 2021.

This is the measure I've created:

qtd_ac = CALCULATE (

COUNTROWS(Table),

FILTER (

ALL(Table),

Table[Year] <= MAX ( Table[Year]) &&

Table[Month] <= MAX ( Table[Month]) &&

Table[Date] <= MAX ( Table[Date])

  )

)

I want a measure that counts the accumulated per month and that this accumulated stops counting when the year ends, because on my dashboard I have a year filter. The accumulated must always be until the 12th month of each year. When it enters January of the next year, the measure must ignore the accumulated count from the previous year.

How can I do that?

[v-yalanwu-msft]

Hi, @Anonymous ;

Is your problem solved? If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yalanwu-msft]

Hi, @Anonymous ;

Try it.

qtd_ac =
CALCULATE (
    COUNTROWS ( Table ),
    FILTER (
        ALL ( Table ),
        Table[Year] = MAX ( Table[Year] )
            && Table[Date] <= MAX ( Table[Date] )
    )
)

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Please find the code

ligne par periode =
VAR yearselected =
    SELECTEDVALUE ( 'Date'[Year] )
VAR monthselected =
    SELECTEDVALUE ( 'Date'[Month] )
VAR mindate =
    DATE ( yearselected, 1, 1 )
VAR maxdate =
    CALCULATE (
        MAX ( 'Date'[Date] ),
        'Date'[Month] = monthselected
            && 'Date'[Year] = yearselected
    )
VAR result =
    CALCULATE (
        COUNTROWS ( Sheet1 ),
        DATESBETWEEN ( 'Date'[Date], mindate, maxdate )
    )
RETURN
    result