Sum of a column in a table variable

[ells]

Apologies if this gets double posted. I have a chart with a trend line for last and current year. I need to get the value of that trend line at a couple of points. 
I strated using the DAX Query behing my chart. That gives me the dax behind the trend calculated for two years. 
This uses SUMMARIZECOLUMS and I just want to SUM the value Banana from the table it creates but I cant as it it not a base table.

Any Suggestions gratefully received.

Thanks

E

// DAX Query
DEFINE
  VAR __DS0FilterTable = 
    TREATAS(
      {"201906",
        "201907",
        "201908",
        "201909",
        "201910",
        "201911",
        "201912",
        "202001",
        "202002",
        "202004",
        "201905"},
      'Date'[CY Month & Year Sort]
    )

  VAR __DS0Core = 
    SUMMARIZECOLUMNS(
			'Date'[CY Month & Year Sort],
      		__DS0FilterTable,
      		"Banana", IF(  MIN('Date'[CY Month & Year Sort]) = "201905" || MIN('Date'[CY Month & Year Sort]) = "201906", 'Measure Trend'[Linear Regression], 0),
      		"Sales", 'Measures Base'[Sales],
      		"Linear_Regression", 'Measure Trend'[Linear Regression]
    )

  VAR __DS0PrimaryWindowed = 
    TOPN(1001, __DS0Core, 'Date'[CY Month & Year Sort], 1)
    

EVALUATE 
  __DS0PrimaryWindowed 

[ells]

Awesome, 

Thanks

[v-jingzhang]

Hi @ells 

You can create a table variable in a measure and use the measure to return the SUM result of a column in this table variable. This may be doable.

Reference:

https://community.powerbi.com/t5/Desktop/DAX-Is-it-possible-to-refer-to-columns-of-a-table-variable/...

Regards,
Community Support Team _ Jing

[ells]

And to get the sum of the values in a table variable? 

The example there does not work for me. I get an error as I can not sum the values of a table that is not a base table?

[v-jingzhang]

Hi @ells 

Sorry for the late reply. SUMX function is able to deal with a table variable. I modified your DAX codes into a measure and tested it with some data. It can accept the table variable and find the [Banana] column in it.

Total = 
  VAR __DS0FilterTable = 
    TREATAS(
      {"202012",
        "202101",
        "202102",
        "202103"},
      'Table'[YearMonth]
    )

  VAR __DS0Core = 
    SUMMARIZECOLUMNS(
			'Table'[YearMonth],
      		__DS0FilterTable,
      		"Banana", IF(  MIN('Table'[YearMonth]) = "202101" || MIN('Table'[YearMonth]) = "202102", SUM('Table'[Budget]), 0)
    )

  VAR __DS0PrimaryWindowed = 
    TOPN(2, __DS0Core, 'Table'[YearMonth], 1)

RETURN
  SUMX(__DS0PrimaryWindowed,[Banana])

I have attached the test PBIX file below. You can download it to see the details.

Regards,
Community Support Team _ Jing

[ells]

@v-jingzhang 
Same errror messages as befgore

The expression specified in the query is not a valid table expression

Only thing I can see different is I dont sum the value (Table[Budget]) as my value is a measure,

E

[ells]

Ok,

I see the SUMX works. However I think this is falling down on the underlying measure, It looks like the trend  line is not helping this calculation.

Thanks

E