Accrue values by date

[Syndicate_Admin]

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:

That is, to accumulate by dates. Can anyone help me? Thank you!

[Anonymous · Accepted solution]

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.

[roppozoppo]

You are a genius!!!! Thank you very very very much! This is what i looking for!!! 

Regards

[aj1973]

Hi @Syndicate_Admin 
add a date table and try this:

Sales running total in Date =

CALCULATE(

    SUM[Monto],

    FILTER(

        ALLSELECTED('Date Table'[Date]),

        ISONORAFTER('Date Table'[Date], MAX('Date Table'[Date]), DESC)

    )

)

[Shaurya]

Hi @Syndicate_Admin,

Instead of using Monto as the field in the matrix you can create this measure and use it:

Cumulative = CALCULATE(SUM('Table'[Monto]), FILTER('Table', DATESBETWEEN('Table'[Date], MIN('Table'[Date]),'Table'[Date])))

Works for you? Mark this post as a solution if it does!
Check out this blog of mine: How to Export Telemetry Data from Azure IoT Central into Power BI

[Syndicate_Admin]

Hello @Syndicate_Admin, thanks for the reply. I did what you told me and this gave me back the Power Bi

It doesn't work for me...

Here is the Excel row

https://files.fm/f/u7h6axkkk

[aj1973]

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]

What am I doing wrong???

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

[aj1973]

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

[Syndicate_Admin]

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?

[aj1973]

Add a calendar Table with DAX

Date Table = CALENDARAUTO(12)

the join to your table

[Syndicate_Admin]