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

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

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