Running Values with Month and Value

[cardonasebast]

Hi. 

I have a problem creating a Running Value in DAX. I need to group by month and value (for this case a Project) but most of the solutions in the community forum are releated to grouping by date and not by date and value as i need in this case.

For example:

The correct Running Value for me must be by month and Project , for example:

 

I used this DAX formulas:

AcumT = CALCULATE(SUM(PptoxMes[Presupuesto]);FILTER(PptoxMes;PptoxMes[Month_Num]<=EARLIER(PptoxMes[Month_Num]));PptoxMes[Año]="2015";values(PptoxMes[Proyecto]))

and this:

AcumT = CALCULATE(SUM(PptoxMes[Presupuesto]);FILTER(PptoxMes;PptoxMes[Month_Num]<=EARLIER(PptoxMes[Month_Num])))

and this solution:

http://community.powerbi.com/t5/Desktop/DAX-Running-total-by-another-column/td-p/10512

and nothing works for me.

I need your help.

Thanks a lot.

[OwenAuger · Accepted solution]

Hi there,

For a running total in a DAX calculated column, I find the safest way is to use ALLEXCEPT to ensure that only the columns you specify remain in the filter context for each running total.

In your case I would use:

AcumT =
CALCULATE (
    SUM ( PptoxMes[Presupuesto] );
    ALLEXCEPT ( PptoxMes; PptoxMes[Año]; PptoxMes[Projecto] );
    PptoxMes[Month_Num] <= EARLIER ( PptoxMes[Month_Num] )
)

Does that do the trick?

Owen 🙂

[Sean · Accepted solution]

@cardonasebast Look at the picture... Both of my formulas are Measures.

EDIT: You should probably add the Year field in the Matrix like this... so data is not aggregated by Month for all years

[OwenAuger · Accepted solution]

Hi there,

For a running total in a DAX calculated column, I find the safest way is to use ALLEXCEPT to ensure that only the columns you specify remain in the filter context for each running total.

In your case I would use:

AcumT =
CALCULATE (
    SUM ( PptoxMes[Presupuesto] );
    ALLEXCEPT ( PptoxMes; PptoxMes[Año]; PptoxMes[Projecto] );
    PptoxMes[Month_Num] <= EARLIER ( PptoxMes[Month_Num] )
)

Does that do the trick?

Owen 🙂