Forum Discussion
Creating a running total
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!
5 Replies
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.
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?
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
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
Hi jarodjp42,
Could you share your sample pbix file and clarify more details?
Regards,
Jimmy Tao
