Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi guys,
I'm still struggling to understand the inner workings of DAX, and I've come across a situation that my mind cant seem to process.
A while ago I posted this problem:
The dataset is like this:
day | program week | Sales |
01/01/2017 | AW01 | 400 |
02/01/2017 | AW01 | 450 |
03/01/2017 | AW01 | 500 |
04/01/2017 | AW01 | 600 |
05/01/2017 | AW01 | 450 |
06/01/2017 | AW01 | 550 |
07/01/2017 | AW01 | 560 |
08/01/2017 | AW02 | 400 |
09/01/2017 | AW02 | 450 |
10/01/2017 | AW02 | 500 |
11/01/2017 | AW02 | 600 |
12/01/2017 | AW02 | 450 |
13/01/2017 | AW02 | 550 |
14/01/2017 | AW02 | 560 |
What I want is a fourth column (measure/column?) that gives me the cumulative sum of these values, but partitioned by program week and ordered by date.
The resulting table should look like this:
day | program week | Sales | Cumulative Weekly |
01/01/2017 | AW01 | 400 | 400 |
02/01/2017 | AW01 | 450 | 850 |
03/01/2017 | AW01 | 500 | 1350 |
04/01/2017 | AW01 | 600 | 1950 |
05/01/2017 | AW01 | 450 | 2400 |
06/01/2017 | AW01 | 550 | 2950 |
07/01/2017 | AW01 | 560 | 3510 |
08/01/2017 | AW02 | 200 | 200 |
09/01/2017 | AW02 | 450 | 650 |
10/01/2017 | AW02 | 500 | 1150 |
11/01/2017 | AW02 | 600 | 1750 |
12/01/2017 | AW02 | 450 | 2200 |
13/01/2017 | AW02 | 550 | 2750 |
14/01/2017 | AW02 | 560 | 3310 |
The answer was this:
CumulativeWeekly = CALCULATE ( SUM ( Table1[Sales] ), FILTER ( ALLEXCEPT ( Table1, Table1[program week] ), Table1[day] <= EARLIER ( Table1[day] ) ) )
Which works really well. However, this stops working as soon as there is a relationship in my original data between the date column and a date table.
Can someone explain why this is happening? I'm not referring to that date table in my formula, why does it have such a big impact on my calculated column? Whats the solution?
Thanks a lot guys, I know its a long read 🙂
Jaap
Solved! Go to Solution.
@Anonymous
Try this method:
Running total of Sales = CALCULATE(SUM('Running total based on category'[Sales]);FILTER(ALL('Running total based on category');'Running total based on category'[Day] <= MAX ( 'TimeDim'[Date] ));VALUES('Running total based on category'[Program week]))
It has the VALUES-formula, which returns the unique values from a column.
Let me know if this works. It should work with your time dimension. I'll also gladly elaborate, if you want me to.
Personally I would not write this as a calculated column as the result can be calculated as a measure on demand. What do you do with this data? What do you mean it stops working? Are you saying the values are not returned to e calc column? I can't see why a that would be.
The result after creating the relationship is the following:
program week | day | Sales | CumulativeWeekly |
AW01 | 01/01/2017 | 400 | 400 |
AW01 | 02/01/2017 | 450 | 850 |
AW01 | 03/01/2017 | 500 | 1350 |
AW01 | 04/01/2017 | 600 | 1950 |
AW01 | 05/01/2017 | 450 | 2400 |
AW01 | 06/01/2017 | 550 | 2950 |
AW01 | 07/01/2017 | 560 | 3510 |
AW02 | 08/01/2017 | 400 | 3910 |
AW02 | 09/01/2017 | 450 | 4360 |
AW02 | 10/01/2017 | 500 | 4860 |
AW02 | 11/01/2017 | 600 | 5460 |
AW02 | 12/01/2017 | 450 | 5910 |
AW02 | 13/01/2017 | 550 | 6460 |
AW02 | 14/01/2017 | 560 | 7020 |
So it seems to forget about that split by program week.
I used this solution because it came from this other user:
https://community.powerbi.com/t5/Desktop/Sum-over-partition-by-order-by-in-DAX/m-p/215856#M95559
If you have a better way by using a measure, I'm all ears!
(The reason I need this, is because I have weekly stock data, and daily sales data, and by subtracting the cumulative sales for that program week from the stock at the beginning of the program week, I hope to calculate daily stock levels)
Hi @Anonymous
Try this measure:
Running total of Sales = CALCULATE(SUM('Test running total'[Sales]);FILTER(ALL('Test running total'[day]); 'Test running total'[day] <= MAX ( 'Test running total'[day] )))
Thats a great measure-based approach, but unfortunately, it still stops working as soon as I link my date table to it 😞
For example, if you add this table to your model:
Date =
ADDCOLUMNS (
CALENDAR (DATE(2000,1,1), DATE(2025,12,31)),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )
)
And then create a relation between the date in your original table and the date in the date table, the Measure stops giving the expected result and gives you plain old cumulatives, not separated by programme week.
Thanks so much for thinking along!
@Anonymous
Try this method:
Running total of Sales = CALCULATE(SUM('Running total based on category'[Sales]);FILTER(ALL('Running total based on category');'Running total based on category'[Day] <= MAX ( 'TimeDim'[Date] ));VALUES('Running total based on category'[Program week]))
It has the VALUES-formula, which returns the unique values from a column.
Let me know if this works. It should work with your time dimension. I'll also gladly elaborate, if you want me to.
Hey Martin, that works perfectly! Thanks a lot!
The date column is the primary key in most tables in my model (its a model full of aggregates mostly, main data is too big to load). Thats why I needed it. It makes everything interact nicely.
Can you elaborate on why you need to filter for the unique values? What extra data is created when we make that relationship, that we then have to filter?
@Anonymous
I'm glad it worked out for you. Don't forget to give me kudos
I understand. Then, of course, you need the time dimension/date table.
It's not like we need to create any new relation or table. We need to have the appropriate conditions: Do this calculation, when row represents some category. When using VALUES(), it returns the unique categories, or, rather, it splits up the calculation between the unique categories. Does that make sense? It's basically like adding conditions to your calculations. example: Calculate some sum, only when category is equal to "AW01".
OK that's pretty clear, but why did creating the relationship with the date table incfluence the outcome of that measure? What changed that made it necessary to change the measure?
Oh, sorry, I misunderstood. The filtering mechanism alters, when you use the dates from the date table. I'm not sure exactly how the mechanism alters, but I know that it depends on what table the date column is located at. This is what I know about the filter mechanism: Imagine you filter one table, which is related to another table with bi-directional filtering. Then you are going to filter both tables, when chosing to filter either on category or on date.
So to sum up I know 2 things about it:
- Filtering mechanism depends on how you created your date table.
- Filtering mechanism depends on the relation, you've created between the tables.
Does that answer your question? I'll gladly elaborate on anything (within my knowledge).
@Anonymous
I'll add the date-table and try to make it work with this. However, I must ask: Is there any good reason for using a date-table right now, seeing as you good the desired result without one?