cummulative not completing correctly per month

[Jonaae]

Hello friends,

can you guys please help me understanding why my measure is not completing correctly per month, the cumulative way is doing correctly, the problem is that is not replicating the value from the previous month when there's not a record in that month.

conteo = 
CALCULATE(
	COUNTA('data'[Nombre certificación]),
    'data'[Nombre certificación] IN {
        "Databricks Certified Associate Developer for Apache Spark 3.0",
		"Databricks Certified Data Engineer Associate",
		"Databricks Certified Data Analyst Associate"
    },
	FILTER(
		CALCULATETABLE(
			SUMMARIZE(
				'data',
                data[Nombre certificación]
			)
		),
		ISONORAFTER(
            'data'[Nombre certificación], 'data'[Nombre certificación], ASC
		)
		),
    ALL ( 'data' ),
    data[Fecha] <= MAX ( 'data'[Fecha] )
)

The one that gives me the following result:

but my expected result should be:

I don't have that much experiencie working with DAX calculation and I don't have that clear what my problem couldbe.

any recommendation please.

[FreemanZ]

hi  @Jonaae

what do you get if you try like:

conteo =

CALCULATE(

        COUNTA('data'[Nombre certificación]),

    'data'[Nombre certificación] IN {

        "Databricks Certified Associate Developer for Apache Spark 3.0",

                "Databricks Certified Data Engineer Associate",

                "Databricks Certified Data Analyst Associate"

    },

    data[Fecha] <= MAX ( 'data'[Fecha] )

)

[Jonaae]

Hello @FreemanZ thanks for your answer.

This is what I get with your recommendation:

It's not getting them acumulatevely per year and  month ( Jan-23) and still not considering the months where there are no records.

[FreemanZ · Accepted solution]

hi @Jonaae 

For the months with no data, it supposed to return BLANK.

If you insist, try to

1) create a date table like this:

DateTable = CALENDAR(MIN(data[Date]), MAX(data[Date]))

2) relate the two tables.

3) plot the Matrix Visual with the DateTable[Date] and a measure like this:

RunningTotal = 
VAR _date = MAX(DateTable[Date])
RETURN
CALCULATE(
    SUM(data[qty]),
    FILTER(
        ALL(DateTable[Date]),
        DateTable[date]<=_date
    )
)

it shall work like this:

sample dataset: