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 have a problem regarding running totals:
1. I have sales data for 2017 and 2018 for every day
2. I want a running total of 2018 with the start value of "Sum of sales 2017"
Solved! Go to Solution.
@Anonymous Please try this as a "New Measure"
Test207 = VAR _2017Sales = CALCULATE(SUM(Test207RunningTotal[Sales]),FILTER(ALL(Test207RunningTotal),Test207RunningTotal[Year]=2017)) VAR _2018Sales = CALCULATE(SUM(Test207RunningTotal[Sales]),FILTER(ALL(Test207RunningTotal),Test207RunningTotal[Year]=2018 && Test207RunningTotal[Date] <= MAX(Test207RunningTotal[Date]))) RETURN IF(MAX(Test207RunningTotal[Year])=2018,_2017Sales + _2018Sales)
Proud to be a PBI Community Champion
@Anonymous Please try this as a New Column
RunningTotal = VAR _2017Sales = CALCULATE(SUM(Test207RunningTotal[Sales]),FILTER(ALL(Test207RunningTotal),Test207RunningTotal[Year]=2017)) VAR _2018Sales = CALCULATE(SUM(Test207RunningTotal[Sales]),FILTER(ALL(Test207RunningTotal),Test207RunningTotal[Year]=2018 && Test207RunningTotal[Date] <= EARLIER(Test207RunningTotal[Date]))) RETURN IF(Test207RunningTotal[Year]=2018, _2017Sales+_2018Sales)
Proud to be a PBI Community Champion
Thank you @PattemManohar for your solution - I wonder if there is a generic solution with a measure since I prefer not to have a calculated column!
/Sebastian
@Anonymous Please try this as a "New Measure"
Test207 = VAR _2017Sales = CALCULATE(SUM(Test207RunningTotal[Sales]),FILTER(ALL(Test207RunningTotal),Test207RunningTotal[Year]=2017)) VAR _2018Sales = CALCULATE(SUM(Test207RunningTotal[Sales]),FILTER(ALL(Test207RunningTotal),Test207RunningTotal[Year]=2018 && Test207RunningTotal[Date] <= MAX(Test207RunningTotal[Date]))) RETURN IF(MAX(Test207RunningTotal[Year])=2018,_2017Sales + _2018Sales)
Proud to be a PBI Community Champion
@PattemManohar - you are a god! 🙂
Thank you so much for your time and your help - it's a solution I really needed, thank you!
🙂
@PattemManohar do you jnow how to do this if the sales for the different years were in different tables?
@Anonymous If you have year wise sales in different tables, then append those into single table using "Append" in Power Query or use "UNION" function in DAX. That will result into single table with overallsales data at one place.
Proud to be a PBI Community Champion
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |