Forum Discussion

DouweMeer's avatar
DouweMeer
Icon for Impactful Individual rankImpactful Individual
6 years ago

YTD with no context

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

 

1A3
2A8
3A7
4A12
5A5
6A1
7A18
1B3
3B5
4B4
5B6
7B9

 

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'?
Need Help

11 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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's avatar
      DouweMeer
      Icon for Impactful Individual rankImpactful Individual
      6 years ago

      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's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        6 years ago

        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.