Running total that uses date slicer

[Anonymous]

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. 

[Anonymous]

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-msft · Accepted solution]

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

[Anonymous]

Hi @Anonymous

Can you please share the sample data in table format ( In Copy - pastable format, not as snapshot)  with expected result ?

Thanks
Rah