Forum Discussion

Jonaae's avatar
Jonaae
Frequent Visitor
3 years ago
Solved

cummulative not completing correctly per month

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's avatar
    FreemanZ
    3 years ago

    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:

     

     

3 Replies

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    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's avatar
      Jonaae
      Frequent Visitor
      3 years ago

      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's avatar
        FreemanZ
        Icon for Super User rankSuper User
        3 years ago

        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: