Last year products saled

[hugoss]

Hi there,

I'm facing a DAX problem I couldn't figured it out. I'm trying to list the products Id saled last year. I have 3 tables: PRODUTOS, CALENDARIO and VENDAS (sales in portuguese), model below.

Then I've created this measue:

lst_id_produtos = 
VAR last_year = MAX(CALENDARIO[Ano]) - 1
VAR tab_sales = CALCULATETABLE(
    VENDAS,
    CALENDARIO[Ano] = last_year
)
VAR col_id_products = SELECTCOLUMNS(tab_sales, [Id Produto])
VAR lst_id_produtos = CONCATENATEX(col_id_products, [Id Produto], ", ")
RETURN lst_id_produtos

When I try to see the list of products id in a visual table, it doesn't work, but it works in a visual KPI (image bellow).

I know the problem is at last_year variable because when I replace last_year by a valid value, it works:

But I dont know how I'm suppose to get last_year value. I've tried SELECTEDVALUE, MIN, MAX and none of them works. Could Any one help me find a solution and give me a summary or a reference doc why it doesn't work with MAX?

Regards

[amitchandak]

@hugoss , as you are already using calendar/date table use time intelligence function for last year. Assume you are using measure M1

Year behind Sales = CALCULATE([M1],dateadd('calendar'[Date],-1,Year))

or

Year behind Sales = CALCULATE([M1],SAMEPERIODLASTYEAR('calendar'[Date]))

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[hugoss · Accepted solution]

Problem solved.

Issue here was filter context. During CALCULATETABLE execution, it was been filtered by the corresponding month at visual table. By adding ALL(CALENDARIO) as parameter to CALCULATETABLE, result was retrieved correctly.

New measure formula is:

lst_id_produtos = 
VAR ano_anterior = MAX(CALENDARIO[Ano]) - 1
VAR tab_produtos = CALCULATETABLE(
    PRODUTOS,
    CALENDARIO[Ano] = ano_anterior,
    CROSSFILTER(VENDAS[Id Produto], PRODUTOS[Id Produto], Both),
    ALL(CALENDARIO)
)
VAR col_id_produtos = SELECTCOLUMNS(tab_produtos, [Id Produto])
VAR lst_id_produtos = CONCATENATEX(col_id_produtos, [Id Produto], ", ")
RETURN lst_id_produtos

Visual table:

[hugoss]

One more thing,

I've changed my measure to:

lst_id_produtos = 
VAR ano_anterior = MAX(CALENDARIO[Ano]) - 1
VAR tab_produtos = CALCULATETABLE(
    PRODUTOS,
    CALENDARIO[Ano] = ano_anterior,
    CROSSFILTER(VENDAS[Id Produto], PRODUTOS[Id Produto], Both)
)
VAR col_id_produtos = SELECTCOLUMNS(tab_produtos, [Id Produto])
VAR lst_id_produtos = CONCATENATEX(col_id_produtos, [Id Produto], ", ")
RETURN lst_id_produtos

Now when my visual table has a year column, it lists the produtcs Id with sales last year (image bellow).

However, when I add a month/year column to my visual table, my [lst_id_products] is always blank.

[hugoss]

Hi @amitchandak ,

Thanks for your reply.

Acctually I can´t use Time Intelligence here because I need to list products id saled last year independently from the month or day. In my visual table, if I add a month/year column, all lines from the same year will have the same list of product Ids, which means dez/2024 have the same list of produtct Ids as nov/2024, as out/2024 and so on and so forth. The product Ids list for 2024 is the list of all product Ids saled in 2023. All months in 2023 have the same produt Ids which is the product Ids saled in 2022. 

Thats why I'm using a SELECTCOLUMNS(CALCULATETABLE(PRODUCT, ...), [Id Product]).

If you ask me why am I trying to do it, it's because my final goal is to use the product Ids list as a filter to my [Total sales this year with sales last year] which will be compared to [Total sales last year], but for [Total sales this year with sales last year] I need to select only products that had sales on previews year (not same period, but all year). So for example, if [Product Id] = 1 had sales only in jan/2023, but in 2024 it has sales in mar/2024 ... dez/2024, [Total sales this year with sales last year] must sum up sales from [Product Id] = 1 in 2024.
If I use time intelligence here [Total sales this year with sales last year] will not be sum up sales for [Product Id] = 1 because sales month in 2023 and 2024 doens't match (they happened in different months)

Any clue on how to solve this problem?