YTD with no context

[DouweMeer]

I bet this has been asked before and I should know the answer as well, but it just escapes me and I can't find it. So I tried to reproduce with the table below. 

Column1 : Time Variable

Column2 : Product variable

Column3 : Quantity variable

1	A	3
2	A	8
3	A	7
4	A	12
5	A	5
6	A	1
7	A	18
1	B	3
3	B	5
4	B	4
5	B	6
7	B	9

Then I created the following measure:

Sum =

VAR a1 = max ( 'Test table'[Time Variable] )

RETURN

calculate (

   sumx ( filter ( 'Test table' , 'Test table'[Time Variable] <= a1 ) , [Quantity variable] )

   , all ( 'Test table'[Time Variable] )

   )

Now the problem, when plotting this measure in a Matrix...

The following returns the same values as the one above this:

Sum =

VAR a1 = max ( 'Test table'[Time Variable] )

VAR a2 = max ( 'Test table'[Product variable] )

VAR t1 =

SELECTCOLUMNS(

   all ( 'Test table' )

   , "Column1" , 'Test table'[Time Variable]

   , "Column2" , 'Test table'[Product variable]

   , "Column3" , 'Test table'[Quantity variable]

   )

RETURN

sumx (

   filter ( t1

      , if ( HASONEVALUE( 'Test table'[Time Variable] ) , [Column1] <= a1 , TRUE() )

      && if ( HASONEVALUE( 'Test table'[Product variable] ) , [Column2] = a2 , TRUE() )

      )

   , [Column3]

   )

the results for product variable 'B' are missing at time variables 2 and 6, as they are missing in the table. The issue is caused by the lack of implicit context. Adding an 'ALL' is like multiplying by 10... on a value of 0. Removing all filters from no records table reference still returns no records. When there is a record, it will act like 'normal'. How can I create the measure in such a way that for product variable 'B' at time variable '2' it will show the return value '3' and at time variable '6' the return value '18'?

[Greg_Deckler]

@DouweMeer - Seems to me that you should create a disconnected table like this:

Table = DISTINCT('CurrentTable'[Time Value])

Or just use GENERATESERIES

Use that as your columns. Then you can grab the value of the current column using MAX/MAXX or SELECTEDVALUE and get everything <= that value from your other table. Should fix you right up.

[DouweMeer]

Would it really need a disconnected table? Otherwise I see the consequence that you would be better off just create the whole table as a disconnected table and use its columns as filters :). 

[Greg_Deckler]

@DouweMeer I'm guessing because apparently: 

Sum =

VAR a1 = max ( 'Test table'[Time Variable] )

RETURN

calculate (

   sumx ( filter ( 'Test table' , 'Test table'[Time Variable] <= a1 ) , [Quantity variable] )

   , all ( 'Test table'[Time Variable] )

   )

Is not returning what you want. And the reason is that since there is no 2 I'm guessing that your a1 is getting set to blank for the intersection of B and 2 and thus, you need a disconnected table so that you always have a value for Time Variable and thus a1.

That's my opinion.

[DouweMeer]

@Greg_Deckler  Nah, it's not the VAR. 

Sum = calculate ( countrows ( 'Test table' ) , ALLSELECTED( ( 'Test table'[Time Variable] ) ) )

 

Sum = calculate ( countrows ( 'Test table' ) , all ( 'Test table' ) )

The table reference of 'Test table' in the 'Sum' measure is just not existing. I know I've dealt with this before. Can't remember whether I had a solution... or I just gave up on it.

[Greg_Deckler]

Well @DouweMeer - nobody can be forced to take advice. See attached PBIX, it really is the VAR. I edited your measure to return a1 instead and you can see the results for yourself. So...

[DouweMeer]

@Greg_Deckler , what I meant with 'Nah, it's not the VAR' is that by 'fixing' the VAR, you won't resolve the problem. 

If I would remove the filter from your calculate expression by so:

Sum =

CALCULATE(

   SUM ( 'Table'[Quantity variable] ),

   ALL( 'Table'[Time Variable] )

   )

You get the following results:

And lord behold, at time variables 2 and 6 for product variable B there a no results. 

It is like arguiing gravity with you (not a perfect comparison). I drop a feather on earth in air or vacuum. Next I try to do the same experiment in space where there is no gravity but I notice 'nothing happens' when I drop the feather in air. And you are like, yea, it is the feather. Here, if I use vacuum, nothing happens either. Like if you would magically transform the feather in a stone, it would 'fall down'. And I'm like, no, if I use it in vacuum nothing happens with the stone either. And you're like: "nobody can be forced to take advice. See attached PBIX, it really is the feather."

As said before, I think the issues lies in the table reference. There is none. Both the VAR  and the RETURN have no rows in their table reference as you can see in your PBIX as in my experiments I mentioned before with the countrows from an earlier message as mentioned above in this comment.  

Let me bounce back your remark 'nobody can be forced to take advice'. True, but nobody can be forced either to see the underlining problem rather than the symptoms they prefer to see because they do understand those. Are you willing to see the underlining problem?

[Greg_Deckler]

@DouweMeer - Not entirely sure I read all of that or caught all of that. It's odd, most people come here to have their problems fixed, not to wax poetic or debate. I already told you how to fix your problem. So, here is a PBIX that implements the solution.

[DouweMeer]

I'm looking for an understandig, not a practical solution. It is a piece of my problem that I filtered from my larger datamodel. I have roughly 10 filters in my dataset that intertwine with the result of my measure. 

I think it is either your perspective of 'us' as users of the forum asking question that we're just looking for a fish rather than try to learn how to fish. Or perhaps it is a cultural barrier where you as an American are more an 'application-first' person than myself being more in the 'concept-first' perspective. 

So far I've been asking for A, you said B but somehow telling me the solution for 'B' is 'B' while it is actually for 'A'. I don't know how you conceive our conversation, all I know is that I consider it as something that you agree with my about the underlining problem of Power BI.

All I see is that the consequence of this limitation would be that you need a shadow dataset in your datamodel with the same values and same relationships, but only used as filters to apply on the original values. 

[Greg_Deckler]

@DouweMeer - After a long bunch of words and things, the question from the original forum post was:

"How can I create the measure in such a way that for product variable 'B' at time variable '2' it will show the return value '3' and at time variable '6' the return value '18'?"

And the answer is that you use the disconnected table trick. I'm not sure what else to tell you. You can't simply invent data that isn't there unless you do something along the lines of the disconnected table trick where you effectively "invent" the rows of data that are missing from the dataset. 

I think the issue is that I look at the problem and say. "Well of course you get that result, what the heck else do you expect? You don't have the data". And you look at the problem and somehow see issues with implicit filters and contexts and other things that, well, I honestly have no idea what you see as the problem. The problem is, you don't have the data. It's very simply, straight-forward and there is no mystery or complexity regarding it. And if you don't have the data, you need to create the data. And the tried and true method of doing that is the disconnected table trick.

Here is another case that you might find interesting regarding time gaps in data. https://community.powerbi.com/t5/Quick-Measures-Gallery/Mind-the-Gap-Irregular-Time-Series/m-p/99179...

[DouweMeer]

@Greg_Deckler 

Thank you for sticking with this question and the other case is an insight that is very interesting. 

This weekend over a barbeque I had a discussion with my brothers about the topic as well and the brother who is in Engineering told me he was 100% certain that the 'issue' I described was an issue on wrongful input which, and he was adamantite, should be resolved by a '0 matrix'. Do you have experience with a '0 matrix' and know whether that principle could be applied in Power BI to fix the issue on hand?

For the rest I can only acknowledge that the lack of understanding of the engine behind it is holding me back and it looks like I need a course in it. Do you perhaps have a suggestion what I could follow to get a better understanding of this (is that Vertipaq, DAX, or something else)?

[Greg_Deckler]

@DouweMeer - A zero matrix is just a matrix of all zeros. I'm not sure how it applies to this situation. However, I can tell you that DAX is generally not suited for matrix algebra. See MMULT as an example. https://community.powerbi.com/t5/Quick-Measures-Gallery/MMULT/m-p/630231#M315