Forum Discussion

cardonasebast's avatar
cardonasebast
Frequent Visitor
10 years ago
Solved

Running Values with Month and Value

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.

Need Help
  • OwenAuger's avatar
    OwenAuger
    10 years ago

    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's avatar
    Sean
    10 years ago

    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

     

2 Replies

  • OwenAuger's avatar
    OwenAuger
    Super User
    10 years ago

    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's avatar
    Sean
    Community Champion
    10 years ago

    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