Creating a running total

[Anonymous]

I am trying to create a column that will display the running total for words. I am looking to sum the words for each vendor.

Example data

vendor	words	Sent to Vendor	anticipated delivery date
andrew	3500	1/1/2017	1/15/2017
andrew	2500	1/5/2017	1/30/2017
andrew	1000	1/21/2017	2/1/2017
bill	200	1/7/2017	1/18/2017
bill	350	1/13/2017	1/19/2017
bill	4500	1/19/2017	1/27/2017

I want to sum the vendor's total words when they get sent to the vendor. But when the anticipated date passes I want to subtract the total from the vendor.

Example with Andrew to better clarify what I am asking for help on:

on 1/15/2017 Andrew should have 6,000 words but on 1/16/2017 Andrew should now lose 3,500 words from his total and only have 2,500. Then when 1/21/2017 date arrives Andrew should now have 3,500 words.  

I am new to power bi and am not sure how this can be set up. Can someone please help me? Thank you in advance!

[v-yuta-msft]

Hi jarodjp42,

To achieve your requirement, create a calculate column using DAX below:

Column = CALCULATE(SUM(Table1[words]), FILTER(ALLEXCEPT(Table1, Table1[vendor]), Table1[Sent to Vendor] <= TODAY() && Table1[anticipated delivery date] >= TODAY()))

 

Regards,

Jimmy Tao

[Anonymous]

@v-yuta-msft

Thank you for your reply. When I use the formula you provided it gives me an error saying that DAX comparison operations do not support comparing values to type text with values of type date. I went through to make sure all my date columns were data type: date and I'm still getting the error. Would the error be caused by the vendor column since it is text and there is no way to change it?

Thanks for your help

[v-yuta-msft]

Hi jarodjp42,

Could you share your sample pbix file and clarify more details?

Regards,

Jimmy Tao

[Greg_Deckler]

Create a separate date table using CALENDAR or CALENDARAUTO and do not link it to your fact table. Use the dates from this table in your visuals. Then you will use something like MAX to create a VAR that grabs your current date. You can then use this in FILTER statements against your fact table to filter down to the correct rows that you need to sum or subtract.

[Anonymous]

@Greg_Deckler

Thank you for your response. I created a date table, but am not sure how to write the proper dax syntax. Could you give me a sample dax syntax I could use?