Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I've been beating my head around the following without getting
a solution. I've read through the boards without finding a similar question.
I created the following running sum calculated column within my table. However, as expected the running sum continues to input data beyond the current date. How would I change the calculated column below to stop the running sum based on <=today() ?
Running Total =
CALCULATE (
SUM ( test1[Produced_Qty]),
ALLEXCEPT ( test1, test1[Work_Center] ),
test1[Date] <= EARLIER ( test1[Date] )
)
Any help is appreciated
Solved! Go to Solution.
Hi @jersey417
Try this
Running Total = IF ( Test1[Date] <= TODAY (), CALCULATE ( SUM ( test1[Produced_Qty] ), ALLEXCEPT ( test1, test1[Work_Center] ), test1[Date] <= EARLIER ( test1[Date] ) ) )
Hi @jersey417,
You can try to use today function to replace the EARLIER part:
Running Total = CALCULATE ( SUM ( test1[Produced_Qty] ), FILTER ( ALLEXCEPT ( test1, test1[Work_Center] ), [Date] <= TODAY () ) )
If above not help, please share some sample data to test.
Regards,
Xiaoxin Sheng
Xiaoxin,
It appears this didn’t solve the overall issues and created more. The code provided appears to have just summed the produced quantity for all dates vs creating a running sum that stops at today’s date.
Based on what you provided:
Work_Center | Date | Day_of_Month | Week_Number | Year | Month | Year_Month | Year_Month_Week | Count | Produced_Qty | Running Total |
TEst1 | 1 | 48 | 2017 | 12 | 201712 | 20171248 | 1 | 448800 | ||
TEst1 | 2 | 48 | 2017 | 12 | 201712 | 20171248 | 1 | 448800 | ||
TEst1 | 3 | 49 | 2017 | 12 | 201712 | 20171249 | 1 | 448800 | ||
TEst1 | 4 | 49 | 2017 | 12 | 201712 | 20171249 | 1 | 212400 | 448800 | |
TEst1 | 5 | 49 | 2017 | 12 | 201712 | 20171249 | 1 | 236400 | 448800 | |
TEst1 | 6 | 49 | 2017 | 12 | 201712 | 20171249 | 1 | 448800 | ||
TEst1 | 7 | 49 | 2017 | 12 | 201712 | 20171249 | 1 | 448800 | ||
TEst1 | 8 | 49 | 2017 | 12 | 201712 | 20171249 | 1 | 448800 | ||
TEst1 | 9 | 49 | 2017 | 12 | 201712 | 20171249 | 1 | 448800 | ||
TEst1 | 10 | 50 | 2017 | 12 | 201712 | 20171250 | 1 | 448800 | ||
TEst1 | 11 | 50 | 2017 | 12 | 201712 | 20171250 | 1 | 448800 |
Redefining what I’m looking for – I’m looking for a running sum(running total column) where the running sum is defined by the following variables; Work Center and Date. However, I need the running sum to stop on today’s date. We have several work_center values and the running sum needs to be independent for each.
Example of what I'm looking for
Work_Center | Date | Day_of_Month | Week_Number | Year | Month | Year_Month | Year_Month_Week | Count | Produced_Qty | Running Total | Day_Of_Week |
TEst1 | 1 | 48 | 2017 | 12 | 201712 | 20171248 | 1 | 0 | 6 | ||
TEst1 | 2 | 48 | 2017 | 12 | 201712 | 20171248 | 1 | 0 | 7 | ||
TEst1 | 3 | 49 | 2017 | 12 | 201712 | 20171249 | 1 | 0 | 1 | ||
TEst1 | 4 | 49 | 2017 | 12 | 201712 | 20171249 | 1 | 212400 | 212400 | 2 | |
TEst1 | 5 | 49 | 2017 | 12 | 201712 | 20171249 | 1 | 236400 | 448800 | 3 | |
TEst1 | 6 | 49 | 2017 | 12 | 201712 | 20171249 | 1 | 4 | |||
TEst1 | 7 | 49 | 2017 | 12 | 201712 | 20171249 | 1 | 5 | |||
TEst1 | 8 | 49 | 2017 | 12 | 201712 | 20171249 | 1 | 6 |
Hi @jersey417
Try this
Running Total = IF ( Test1[Date] <= TODAY (), CALCULATE ( SUM ( test1[Produced_Qty] ), ALLEXCEPT ( test1, test1[Work_Center] ), test1[Date] <= EARLIER ( test1[Date] ) ) )
Perfect - This is the solution I was looking for. Thanks for the help.
I just wanted to follow up on the following. I switched my data to a directquery and the following doens't appear to work due to the EARLIER function being used. Based on the information that I read online it appears that the EARLIER function is not avaialble when using a directquery. Is there away around this?
Running Total =
IF (
Test1[Date] <= TODAY (),
CALCULATE (
SUM ( test1[Produced_Qty] ),
ALLEXCEPT ( test1, test1[Work_Center] ),
test1[Date] <= EARLIER ( test1[Date] )
)
)
Try
Running Total = VAR myDate = test1[Date] RETURN IF ( Test1[Date] <= TODAY (), CALCULATE ( SUM ( test1[Produced_Qty] ), ALLEXCEPT ( test1, test1[Work_Center] ), test1[Date] <= myDate ) )
I'm not getting the returned error of "Function "CALCULATE" is not allowed as part of calculated column DAX expression on DirectQuery models. "
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |