Summarized table from two tables

[Sofinobi]

hello community
please if you can help me,

i have 2 tables : Product and Date
i want to create a summarized table that have a column from Product table (Product) and the (Month year from Date Table)
result like this image (every product in every month)

and then insert the measures to separate the values of each Product and Month.
Average.pbix 
thank you

[wdx223_Daniel · Accepted solution]

never use SUMMARIZE to add columns!

OverStock Table = ADDCOLUMNS(SUMMARIZE(Sales_Tab,'Product'[Product],'Date'[Month Year]),"AVG Stock",'Measure'[AVG Stock Qte Month],"AVG Sales Qte R3M",'Measure'[AVG Sales Qte R3M],"AVG Price",'Measure'[AVG Price Month])

[wdx223_Daniel · Accepted solution]

never use SUMMARIZE to add columns!

OverStock Table = ADDCOLUMNS(SUMMARIZE(Sales_Tab,'Product'[Product],'Date'[Month Year]),"AVG Stock",'Measure'[AVG Stock Qte Month],"AVG Sales Qte R3M",'Measure'[AVG Sales Qte R3M],"AVG Price",'Measure'[AVG Price Month])

[Sofinobi]

thank you so much @wdx223_Daniel thats exactely what i want, i'll try to learn more about ADDCOLUMNS and SUMMARIZE. 
please, just one more thing if you can; 
the measure [AVG Stock Qte Month] dont show any value when there is not sales in that month, in this case, i want to show me the value of last month,
this image

in this image the product "AMLI 30" in May 22 was 929,00, i need the same value in June 22 
thank you very very much

[wdx223_Daniel]

GENERATE (
    VALUES ( 'Product'[Product] ),
    VAR _max =
        CALCULATE ( MAX ( 'Sales_Tab'[CreationDate] ) )
    RETURN
        ADDCOLUMNS (
            CALCULATETABLE ( VALUES ( 'Date'[Month Year] ), 'Date'[Date] <= _max ),
            "Avg Stock",
                VAR _date =
                    CALCULATE ( MAX ( 'Date'[Date] ) )
                VAR _mon =
                    FORMAT (
                        MAXX (
                            FILTER ( ALL ( 'Date'[Date] ), [AVG Stock Qte Month] && 'Date'[Date] <= _date ),
                            'Date'[Date]
                        ),
                        "mmm yy"
                    )
                RETURN
                    CALCULATE ( 'Measure'[AVG Stock Qte Month], 'Date'[Month Year] = _mon )
        )
)

AVG Stock Qte Month = AVERAGEX(Sales_Tab,Sales_Tab[OldLogicalQuantity])

[Sofinobi]

thank you @wdx223_Daniel  for your answer.
your solution works, but it doesn't give me result in last months (image attached)

on Dec 22 and Jan 23 i have result only for "MYCO"
thank you very much

[wdx223_Daniel]

OverStock Table3 = 
GENERATE (
    SUMMARIZE ( 'Sales_Tab', 'Product'[Product] ),
    ADDCOLUMNS (
        VALUES ( 'Date'[Month Year] ),
        "Avg Stock",
            VAR _date =
                CALCULATE ( MAX ( 'Date'[Date] ) )
            VAR _mon =
                FORMAT (
                    MAXX (
                        FILTER ( ALL ( 'Date'[Date] ), [AVG Stock Qte Month] && 'Date'[Date] <= _date ),
                        'Date'[Date]
                    ),
                    "mmm yy"
                )
            RETURN
                CALCULATE ( 'Measure'[AVG Stock Qte Month], 'Date'[Month Year] = _mon )
    )
)

[Sofinobi]

thank you very much @wdx223_Daniel  that is perfect, you helped me a lot.
if i need yto add a column from Date table, is this syntax correct?

VALUES ( 'Date'[Month Year],'Date'[YearMonth Number] )

 tank you so much my friend, i realy apreciate your help

[wdx223_Daniel · Accepted solution]

GENERATE (
    SUMMARIZE ( 'Sales_Tab', 'Product'[Product] ),
    ADDCOLUMNS (
        SUMMARIZE ( 'Date', 'Date'[Month Year], 'Date'[YearMonth Number] ),
        "Avg Stock",
            VAR _date =
                CALCULATE ( MAX ( 'Date'[Date] ) )
            VAR _mon =
                FORMAT (
                    MAXX (
                        FILTER ( ALL ( 'Date'[Date] ), [AVG Stock Qte Month] && 'Date'[Date] <= _date ),
                        'Date'[Date]
                    ),
                    "mmm yy"
                )
            RETURN
                CALCULATE ( 'Measure'[AVG Stock Qte Month], 'Date'[Month Year] = _mon, REMOVEFILTERS('Date') )
    )
)

[Sofinobi]

thank you very much @wdx223_Daniel that's perfect, excately what i'm looking for,

you are the best my friend, thank you again

[Sofinobi]

hi all,
finnaly i find a sollution for my table, two expressions give the correct values but it still one expression doesn't show any value

do you have any idea what is the problem?
average3.pbix 
thank you

[DimaMD]

Hi @Sofinobi I faced the same problem solving your problem. Let's try to figure out what is wrong with you.
What do you want to see the correct total when you calculate ( [AVG Stock Qte Month]-[AVG Sales Qte Month] ) * [AVG Price month],
Can you provide your expected result?

[DimaMD]

Hi @Sofinobi Look at the file, I hope I understood your task correctly

[Sofinobi]

hi @DimaMD thank you for your answer,

but it isn't what i'm looking for
i need a separate table, not a matrix (or visual)
i need that for my final result, that i will calculate a measure = 
( [AVG Stock Qte Month]-[AVG Sales Qte Month] ) * [AVG Price month]
because actualy when i do this calculation, my Averages measures calculate all the values in a column, not averages of each product separately
thats why i need a table to separate them
thank you