Forum Discussion
Microsoft EmployeeNeed help with measure
I have table that has dates and values. I know how to create a running total using the well known DAX pattern. However, I want a user to be able to select a single date (from a slicer) and see the running total as of that date. Where I am stuck is how to create a a measure or calc column that, when filtered by a slicer, will perform the running total on all records where [Date] < selected slicer Date.
In other words, how do you created a calc column/table or measure that will show (or sum, or running total, etc.) all earlier values based on a slicer selection?
7 Replies
- MattAllington
Community Champion
I'm not aware of a pattern that does what you say. Here is the pattern I use (measure)
Asusming you have a calendar table that follows the rules. http://exceleratorbi.com.au/power-pivot-calendar-tables/
Lifetime to date = calculate(sum(data[value],filter(all(calendar[date]),calendar[date]<=max(calendar[date])))
- dkay84_PowerBI
That measure is for a running total. However, if you select a single date the running total will be for just that date. I need to return the running total as of that date (not just for that date).
How would one return a table that has all records where some value <= a slicer? If I could understand that, then nesting a running total measure in that formula wouldn't be too hard.
- MattAllington
dkay84_PowerBI wrote:That measure is for a running total. However, if you select a single date the running total will be for just that date. I need to return the running total as of that date (not just for that date).
How would one return a table that has all records where some value <= a slicer? If I could understand that, then nesting a running total measure in that formula wouldn't be too hard.
No. it is a life time to date running total that does what you asked.
It works because of the All() function that removes the filter context. If you want to learn a lot about dax quickly, then you may like to take a look at my book.
