Monthly cumulative sum

[DVHeld]

Hi, I'm new to Power BI, and I have stumbled upon a problem. I have scoured lots of other suggestions but so far nothing has worked for me. I have a V_CTACTE_HORAS table that has a "month" (MES) column that contains YYYY-MM in string format. I have created a date (FECHA) column like so:

FECHA = DATEVALUE(V_CTACTE_HORAS[MES])

I have used this new column to make a relation to my date dimention table (D_TIEMPO), created with the CalendarAuto() function, also marked as a date table. The V_CTACTE_HORAS table also has a integer credit (ABONO) column and an integer debit (CARGO) column, and it has a proyect ID column (PRY_ID). I need to calculate the per proyect monthly cumulative sum of both, and then substract the debits from the credits, to get the monthly cumulative difference. The substraction part should be easy, but I'm stuck on the cumulative sum part. Every suggestion I've found has so far not worked. Some approches (like this one) yield me this:

Some other approaches I have tried give me an empty column. What I need is actually this:

[v-shex-msft]

Hi @DVHeld ,

Can you please share a pbix file for test? It is hard to test without any sample data.

Regards,

Xiaoxin Sheng

[DVHeld]

Thanks, here is the file: https://1drv.ms/u/s!AjKjNv73ICO9gbIHVaealPlYKsmJXg.

I have achieved part of what I intended, namely to be able to get the monthly cumulative sum, I did it this way:

Abono Acumulado Mensual = 
CALCULATE (
    SUM ( V_CTACTE_HORAS[ABONO] ),
    FILTER ( 
        ALL ( V_CTACTE_HORAS ),
        V_CTACTE_HORAS[ORDEN] <= MAX ( V_CTACTE_HORAS[ORDEN] )
    ),
    VALUES ( V_CTACTE_HORAS[MES] ),
    VALUES ( V_CTACTE_HORAS[PRY_ID] )
)

but now I need to get the total cumulative sum, and I'm having trouble with that. I tried this:

Abono Acumulado = 
SUMX(
    FILTER(
        ALLSELECTED( V_CTACTE_HORAS[MES] ),
        V_CTACTE_HORAS[MES] <= MAX( V_CTACTE_HORAS[MES] )
    ),
    [Abono Acumulado Mensual]
)

but did not work, just shows me the value of [Abono Acumulado Mensual]. I also tried with the same formula as the monthly cumulative sum, but removing this line:

VALUES ( V_CTACTE_HORAS[MES] ),

but that did not work either.

I will soon begin to properly study DAX, as I have now realised it works very differently from what I expected, but right now I need to get this one thing done, so any help is greatly appreciated!

[v-shex-msft]

HI @DVHeld ,

You can try to use following measure if it works:

Abono Acumulado2 = 
var currMES=MAX(V_CTACTE_HORAS[MES] )
Return
SUMX(
    FILTER(
        ALLSELECTED( V_CTACTE_HORAS),
        LEFT(V_CTACTE_HORAS[MES],4)=LEFT(currMES,4)&&RIGHT(V_CTACTE_HORAS[MES],2)<=RIGHT(currMES,2)
    ),
    [Abono Acumulado Mensual]
)

Regards,

Xiaoxin Sheng