Forum Discussion
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:
That is, to accumulate by dates. Can anyone help me? Thank you!
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.
11 Replies
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.
You are a genius!!!! Thank you very very very much! This is what i looking for!!!
Regards
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 BIHello @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
Hi Syndicate_Admin
First you need to add a Date Tablehere is your DAX
Sum of Numbre running total in Date =VAR _NOMBRE = SELECTEDVALUE(Hoja1[NOMBRE])RETURNCALCULATE(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
- 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)))
