Forum Discussion
Running total that uses date slicer
Hi,
I am read several posts about trying to come up with a running total that uses a date slicer. I keep finding the same dax formula and it is not helping me with my situation.
Running Total =
CALCULATE (
SUM ( Table[column] ),
FILTER ( ALL ( Table ), Table[columnDate] <= MAX ( Table[ColumnDate] ) )
)
I am trying to set up a clustered column graph that will show a running total of words in progress on filters applied -- a date slicer. I want to see my running total lower when I adjusted the date on my date slicer.
Is there anyone that can help me accomplish this? Any assistance or guidance is greatly appreciated!
Thank you!
Example, I want to have a running total of 5000 on 7/25/18. The next day 1000 words are delivered, so when I change my date slicer to 7/26/18 I want my running total to display 4000.
Hi Anonymous
create a new table with fomula below, then create a measure in that table, next edit relationship between this new table and your original data table, finally add the column "Finish Date" from this new table to the slicer.
Table = VALUES(Table1[Finish Date]) selected = SELECTEDVALUE('Table'[Finish Date])Then create a measure in your data table
Measure = CALCULATE ( SUM ( Table1[Words] ), FILTER ( ALLEXCEPT ( Table1, Table1[Person] ), [Finish Date] <= MAX ( Table1[Finish Date] ) && [Finish Date] >= [selected] ) )Best Regards
maggie
3 Replies
Hi Anonymous
Thank you for responding to my post. Here is some sample data.
Person Words Start Date Finish Date Running Total ABS 2500 4/27/2018 5/1/2018 2,500 ABS 2300 4/27/2018 5/2/2018 4,800 ABS 500 5/13/2018 5/26/2018 5,300 ABS 1500 5/10/2018 5/31/2018 6,800 ABS 2300 6/10/2018 6/26/2018 9,100 ABS 1000 7/1/2018 7/15/2018 10,100 ABS 1200 7/3/2018 7/18/2018 11,300 AC 100 3/10/2018 3/24/2018 100 AC 400 4/1/2018 4/16/2018 500 AC 900 4/12/2018 4/29/2018 1,400 AC 100 4/28/2018 5/8/2018 1,500 AC 1000 5/5/2018 5/20/2018 2,500 AD 1200 4/2/2018 4/19/2018 1,200 AD 1600 4/20/2018 5/1/2018 2,800 AD 800 5/6/2018 5/16/2018 3,600 AD 2300 5/19/2018 6/1/2018 5,900 AD 4000 6/2/2018 6/15/2018 9,900 AD 3500 6/7/2018 6/23/2018 13,400 AD 3000 7/1/2018 7/17/2018 16,400 Person Running Total ABS 49,900 AC 6,000 AD 53,200 Person Running Total using slicer ABS 42,600 AC 4,000 AD 49,200 I am looking for a formula that will work with the date slicer on my visuals. I want to look at a specific date. For example, 5/1/2018. I want to have power bi do a running total for everything that is still in progress. My first table is the sample data. The second table is what the running total should be without slicing any date. The third table is looking at everything after 5/1/2018.
I am slicing the date using the Finish Date. When I adjust my slicer to the specific date of 5/1/2018 all the projects that were completed before 5/1/2018 should no longer be added to the running total (The third table).
As mentioned earlier all the posts I have read keep giving the same formula as
Running Total =
CALCULATE (
SUM ( Table[column] ),
FILTER ( ALL ( Table ), Table[columnDate] <= MAX ( Table[ColumnDate] ) )
)However, this is not giving me my needed outcome. Any advice you can give will be much appreciated! Thank you!
v-juanli-msftCommunity Support
Hi Anonymous
create a new table with fomula below, then create a measure in that table, next edit relationship between this new table and your original data table, finally add the column "Finish Date" from this new table to the slicer.
Table = VALUES(Table1[Finish Date]) selected = SELECTEDVALUE('Table'[Finish Date])Then create a measure in your data table
Measure = CALCULATE ( SUM ( Table1[Words] ), FILTER ( ALLEXCEPT ( Table1, Table1[Person] ), [Finish Date] <= MAX ( Table1[Finish Date] ) && [Finish Date] >= [selected] ) )Best Regards
maggie