Relationship influences outcome of calculated column

[Anonymous]

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 

[MattAllington]

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. 

[Anonymous]

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)

[Anonymous]

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] ))) 

[Anonymous]

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 · Accepted 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.

[Anonymous]

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]

@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".

[Anonymous]

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?

[Anonymous]

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]

@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?