Last Year Products Traded

[hellind]

Can some one help with the measure?

My data model is

[Dates] table as Calendar lookup Table.

[Products] as Products  lookupTable

'Volume SC' is the data table

This measure concatenates the product traded For Previous Fiscal Year = 2018, the products traded are zero.

For current Fiscal Year = 2019, the products traded are 'Stocks', for example.

This measure is supposed to return the products traded the PREVIOUS YEAR 2018 BUT it returns the products traded this year 2019. Seems the FILTER(ALL(DATES) is not working.

List of Products Category Traded:=

VAR ProductsLY = CALCULATETABLE(VALUES( PRODUCTS[ProductCategory]) ,

     FILTER( ALL(DATES), [Fiscal Year] = MAX(Dates[Fiscal Year]) - 1 ) ,

     FILTER ('Volume SC', [Traded Volume] > 0) )

RETURN CONCATENATEX(ProductslY, [ProductCategory], ",")

[v-yulgu-msft]

Hi @hellind ,

You should remove the relationship between 'DATES' table and 'Volume SC' table. Then, the formula should be modified similar to below.

List of Products Category Traded =
VAR ProductsLY =
    CALCULATETABLE (
        VALUES ( PRODUCTS[ProductCategory] ),
        FILTER (
            ALLSELECTED ( 'Volume SC' ),
            'Volume SC'[Fiscal Year]
                = MAX ( Dates[Fiscal Year] ) - 1
                && [Traded Volume] > 0
        )
    )
RETURN
    CONCATENATEX ( ProductslY, [ProductCategory], "," )

Best regards,

Yuliana Gu

[GilbertQ]

Hi there

Could it not be that you explained that in 2018 "The products traded are zero" and in your measure you are filtering out data where the Traded Volumne > 0?

[hellind]

In the pivot filter, I have selected Fiscal Year = 2019

Therefore this measure is supposed to return for the previous year Fiscal Year = 2018

This line of code is supposed to look back one year:

FILTER (ALL(Dates),
(Dates[Fiscal Year]) = MIN(Dates[Fiscal Year]) - 1
)

But it is not taking back one year.

[hellind · Accepted solution]

My data-model is a star schema.

[Products] is a Dimension table with a one-direction relationship flowing down to fact table 'Volume SC'

Therefore, the solution was, to use CROSSFILTER, the amemded code below works, during the execution of the code the filter will flow up back to Products table to concatenate the values of Product Category.

Thanks to everyone who tried to help.

List of Products Category Traded:=

VAR ProductsLY = CALCULATETABLE(VALUES( PRODUCTS[ProductCategory]) ,

     CROSSFILTER(PRODUCTS[Product], 'Volume SC'[Product], Both)

     FILTER( ALL(DATES), [Fiscal Year] = MAX(Dates[Fiscal Year]) - 1 ) ,

     FILTER ('Volume SC', [Traded Volume] > 0) )