Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
3 years ago
Solved

Accrue values by date

Hello everyone. I'm starting with this Power Bi thing. I have this table: in Power Bi see it like this: What I want is for them to accumulate to see it like this: ...
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi roppozoppo ,

     

    The solution is a bit complex but I made it.

    And it is done in Power Query.

    1.Copy a main table, then delete the other columns, and keep a distinct field, NOMBRE.

     

    2.Add an index column. It's to sort the NOMBRE column.

     

    3.Add a custom column to crossjoint the date table. The date table is Table (3). It contains all distinct dates from main table. Expand it.

    4.Merge the new table with the main table as follows. Expand the MONTO column.

    5.Fill down the MONTO column group by the NOMBRE column.

     

    6.Filter out the null of the MONTO column.

     

    7.Sort the NOMBRE column by the Index column. Here's the result.

     

    You can download the attachment for details.

     

    Best Regards,

    Stephen Tao

     

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

aj1973's avatar
aj1973
Community Champion
3 years ago

Hi Syndicate_Admin 
First you need to add a Date Table

 

here is your DAX

Sum of Numbre running total in Date =
VAR _NOMBRE = SELECTEDVALUE(Hoja1[NOMBRE])
RETURN
CALCULATE(
    SUM(Hoja1[MONTO]),
    FILTER(
        ALLSELECTED('Date Table'[Date]),
        ISONORAFTER('Date Table'[Date], MAX('Date Table'[Date]), DESC)
    )
    , Hoja1[NOMBRE] = _NOMBRE
)
 

If you wanted to add 0 instead of Blanks then put the formula into IF condition

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    3 years ago

    What am I doing wrong???

    I'm making a measure and it doesn't work for me

    • aj1973's avatar
      aj1973
      Community Champion
      3 years ago

      Add the ' )' in SUM function right before the comma 

      • Syndicate_Admin's avatar
        Syndicate_Admin
        Administrator
        3 years ago

        I added ")" but below I see that you put TABLE (DATE) and I do not have a table. I have a date column that is on SHEET1(DATE). Maybe you need to do something to create a separate table with the date?