Sum over partition by order by in DAX

[Anonymous]

Hey guys,

I'm struggling to think of a way to solve my problem in DAX. In SQL I'd use sum over partition by order by, but in DAX I dont know what the equivalent is.

The dataset is like this:

day	program week	Sales
01/01/2017	AW01	400
02/01/2017	AW01	450
03/01/2017	AW01	500
04/01/2017	AW01	600
05/01/2017	AW01	450
06/01/2017	AW01	550
07/01/2017	AW01	560
08/01/2017	AW02	400
09/01/2017	AW02	450
10/01/2017	AW02	500
11/01/2017	AW02	600
12/01/2017	AW02	450
13/01/2017	AW02	550
14/01/2017	AW02	560

What I want is a fourth column (measure/column?) that gives me the cumulative sum of these values, but partitioned by program week and ordered by date.

The resulting table should look like this:

day	program week	Sales	Cumulative Weekly
01/01/2017	AW01	400	400
02/01/2017	AW01	450	850
03/01/2017	AW01	500	1350
04/01/2017	AW01	600	1950
05/01/2017	AW01	450	2400
06/01/2017	AW01	550	2950
07/01/2017	AW01	560	3510
08/01/2017	AW02	200	200
09/01/2017	AW02	450	650
10/01/2017	AW02	500	1150
11/01/2017	AW02	600	1750
12/01/2017	AW02	450	2200
13/01/2017	AW02	550	2750
14/01/2017	AW02	560	3310

Thanks for the help guys!

Jaap

[Vvelarde · Accepted solution]

@Anonymous

Hi, Create a Calculated Column:

CumulativeWeekly =
CALCULATE (
    SUM ( Table1[Sales] ),
    FILTER (
        ALLEXCEPT ( Table1, Table1[program week] ),
        Table1[day] <= EARLIER ( Table1[day] )
    )
)

Let me know if you need more help

Regards

Victor

Lima - Peru

[Anonymous]

@Vvelarde Hola Victor, tengo un problema similar, agradecería la ayuda 🙂

Tengo un campo llamado ID, el cual corresponde a los ID de cada negocio y un campo fecha o mes.  Necesito crear un contador por ID y ordenarlo por fecha. Esto sería un ejemplo de lo mencionado:

ID	mes	Valor	Contador
111	3	1	1
111	2	1	2
111	1	1	3
222	3	1	4
222	4	1	3
222	5	1	2
222	6	1	1
33	1	1	2
33	2	1	1

[Vvelarde]

@Anonymous

Hola, Según veo tu contador es por ID y la Fecha de Mayor a Menor. Correcto?

Te puedo preguntar cual es el sentido de lo que buscas.

Saludos

[Anonymous]

@Vvelarde Claro, debo dejar los ID más actuales.

Esto lo necesito para poder reflejar esta información en una pestaña y filtrar por los que tienen el contador 1. De esta manera dejaría los más actuales y el resto no serán considerados.

Gracias!

[Vvelarde]

@Anonymous

Try with this

Orden =
COUNTROWS (
    FILTER (
        Table1;
        Table1[ID] = EARLIER ( Table1[ID] )
            && Table1[mes] >= EARLIER ( Table1[mes] )
    )
)

Spoiler

[Anonymous]

Hi Victor,

Here data is in ascending order by date, can we achieve the same if data unoriented.

I mean without using ordered data. I am struggling for the same.

Regards,

Uma

[Anonymous]

My hero lives in Lima, and his name is Victor!

Do you happen to know any sources that kind of list SQL equivalents of functions in DAX? I'm still strugging to think the DAX way instead of the more straightforward SQL way....