Accumulated count of rows by month

[Anonymous]

Hello everyone !

I have the following problem: 

This is my chart which count the number of errors that a machine had during the last year, based on a DAX that I have created before. 

As you can see, it shows me the count by month but now, I'd like to create a measure that calculate the accumulated sum by month excluding the previous month. 

For example: 

January: Shows the sum of every month till the end of the year because there isn't previous month before it = 8

February: Every month except January = 7

March = Every month except January and February = 7 ( I know I don't have data for March but it doesn't matter) 

.....

And that logic till the end of the year

Many thanks in advanced!

Cheers

[dax]

Hi APASTORM,

You could try to refer to my sample and use below meausre to see whether it work or not

Measure =
CALCULATE (
    SUM ( 'Table'[AMOUNT] ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[NAME] ),
        'Table'[MONTH] >= MIN ( 'Table'[MONTH] )
    )
)

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ibarrau]

Hi, you can do several things. The lazy one should be using a waterfall chart. If you want to use it in any chart you can create a measure like this:

Measure = 
CALCULATE (
    COUNT ( Table[ColumnToCount]
    , DATESYTD ( DateTable[DateColumn] )
)

NOTE: Consider you should have a Date Table or Data Dimension. You can learn how to create one here. With this Date column day by day you can do time intelligence like the example.

Regards,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @ibarrau!

Firstly, thank you for your answer.

I have tried the waterfalll chart but it isn't what I'm looking for.

And about the second solution you have written: 

I'm looking for something like that but I need to implent my formula into the measure I'm trying to create because my formula has some conditions in it. 

This is the formula I'm using to count: 

MES 05 =

VAR FINDE = SELECTEDVALUE('TABLA CALENDARIO'[Date])

RETURN

COUNTROWS(

FILTER(

'DETALLES EMPLEADOS NT' ; 'DETALLES EMPLEADOS NT'[GL BUEN]="05" &&

(('DETALLES EMPLEADOS NT'[FECHA CORREGIDA] > FINDE ) || (('DETALLES EMPLEADOS NT'[FECHA CORREGIDA] < FINDE ) && ('DETALLES EMPLEADOS NT'[FIN CUPO] < FINDE)))))

P.D: I already have a calendar table created 

Cheers

[ibarrau]

¿Podrías intentar de explicar que tenes que medir? porque la DAX que enviaste no tiene mucho sentido dado que la pensaste para solucionar esto. Intentá regresar al requerimiento sin pensar en tu DAX y responde ¿Que tenes que acumular en las barras? cuando sepas eso podes ponerlo dentro de la medida que yo te pase en lugar de COUNT(Tabla[columna]).

Si no puedes resolver eso tampoco, contanos más del caso puntual, porque con el YTD se soluciona lo que preguntas pero no entiendo lo que queres poner ahi, podrías explicarlo en palabras.

Saludos

[Anonymous]

@ibarrau 

Vale a ver, el DAX que envié es antiguo porque era una de las formas que se me ocurrieron para solucionar el problema.

Como ves tengo un gráfico de barras que cuenta los tipos de errores que tiene una máquina a lo largo de un año. Cada error tiene una fecha concreta que obviamente se encuentra dentro de un mes. 

Lo que me gustaría hacer sería hacer una suma acumulativa de dichos errores desde una fecha que yo seleccione (esta fecha será un filtro con las fechas finales de cada mes) hasta final de año. 

Por ende, si nos fijamos en el ejemplo y seleccionamos el 28 de Febrero, en todos los meses restantes (excepto Enero porque es anterior a Febrero) hasta Diciembre deberían aparecer la suma de los errores que tenemos en el resto de los meses, es decir: 

• 3 errores "08"
• 4 errores "05"

Otro ejemplo; Si selecciono el 30 de Abril, ya no me debería de contar los datos de meses anteriores y la contabilización de errores se debería quedar de la siguiente forma:

• 4 errores "05"
• 2 errores "08"

Espero haberme explicado claramente para que puedas entenderlo.

Muchas gracias!

P.D: Podríamos decir que es un YTD pero a la inversa; en Enero se me ha de mostrar todos los fallos que he tenido hasta Diciembre y en Diciembre sólo los datos de este último mes

[dax]

Hi APASTORM,

If possible, could you please inform me your sample and your expected output? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi again @dax !

I have just checked the file you attached before and that's exactly what I'm looking for!! But I need that if there's a month with no errors, that month should show the errors from the last month. 

I mean, in your example between the month 2 and 7 there are no values because there are no errors in that months, could be possible if in that months appears the value "8"?

Thank you again!

[ibarrau · Accepted solution]

Hi, I couldn't find the file you are mentioning, but regarding your last reply mentioning an inverted YTD, there is no automatic function for that.

I have built an inverted cumulative function. You can add a range filter and it will cumulate by that filter.

Function:

Cum_MIN = 
CALCULATE (
    CALCULATE ( COUNT (Table[Column]); Table[Error] = "05" ) ;
    FILTER (
        ALLEXCEPT( D_Dates; D_Dates[Dates]; D_Dates[Year] );
        D_Dates[Monthnum] >= MIN ( D_Dates[Monthnum] )
    )
)

Assuming D_Dates is your calendar table.

You can add it this way:

It should also draw the same value of the next month if there are no errors.

Hope this helps. If I could check the attach I may be able to help more.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you @ibarrau ! It works perfectly!

But is there any option to insert the following conditions into de DAX you've created?

'DETALLES EMPLEADOS NT'[error]="05" &&

(('DETALLES EMPLEADOS NT'[DATE1] > FINDE ) || (('DETALLES EMPLEADOS NT'[DATE1] < FINDE ) && ('DETALLES EMPLEADOS NT'[DATE FIXED] < FINDE)) 

FINDE is a VAR that I've created an its = SELECTEDVALUE(CALENDAR TABLE[DATE])

Many thanks!

[ibarrau]

I'm sorry but I don't understand why you keep using FINDE. I thought that the VAR was just an idea for the cumulative problem we just solved.

Anyway, try using FILTER function inside the CALCULATE for the count. Filter your table with those conditions. I'm not sure if that will work, you are manipulating a lot the dates.

Regards,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

It's complicated to be explained because the errors of the machines can't be counted anyhow, the errors are counted under the conditions I have posted.

By the way, you have solved my problem! 

Many thanks!!

Other question: Is it possible that the months which hasn't got errors show the count of errors of previous months? 

I mean

Between January and April: February and March should show 3 errors.

Between April and June: May should show 3 errors

Between June and September: July and August should show 2 errors

I'm using the dates from the date table as filter

Thank you again ! You've been really helpfull

[ibarrau]

Great!

In my testing I have used a Calendar Table with Year and Month columns created. This year and month were drawn when they hadn't values with the same value of next month.

What year/month are you setting on the axis of the visualization? You may change that and solve it.

Regards,

[Anonymous]

Hi again @ibarrau !

Sorry for being so annoying.. 

You were right, I corrected the date I was setting on the axis and the problem dissapeared but I do not know why the chart just display data till the last month (in this case October) and not till the end of the year

Is maybe because of the DAX? 

Kind regards

[Anonymous]

Hi @dax ! 

Sorry for not answering you before..

Okay so this is the data: (each row is a different machine)

And this are the conditions that I introduce to the measure to count the number of errors that later are displayed in the chart.

There are two types of machine: "05" and "08" so the below DAX is exactly the same but replacing "05" to "08"

05 =

VAR FINDE = SELECTEDVALUE('TABLA CALENDARIO'[Date])

RETURN

COUNTROWS(FILTER('DETALLES';'DETALLES'[MACHINE TYPE]="05" &&

(('DETALLES'[DATE1] > FINDE ) || (('DETALLES'[DATE1] < FINDE ) && ('DETALLES'[DATE FIXED] < FINDE)))))

The VAR FINDE is the date that I select from a date filter which contains all the ends of each month (31/01 for January, 28/02 for February, etc).

What I would like to do is to counting the erros that I have had during a year in a accumulated way depending on the date that I have selected in the filter, I mean:

If a select 31/01 the chart should display all the errors that I have had from February 1st till December 31st so if we take a look at the chart I attached before the result must be: 

• 3 erros in the "08" machine 
• 4 errors in the "05" machine

Another example would be; If I select May 31st the chart should display all the errors from June 1st till December 31st so the results must be:

• 2 errors in the "08" machine
• 4 erros in the "05" machine

Hope you could understand me..

Many thanks for your time.

Cheers!!

Adrian