Forum Discussion

Sofinobi's avatar
Sofinobi
Icon for Helper IV rankHelper IV
3 years ago
Solved

Summarized table from two tables

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 Tabl...
  • wdx223_Daniel's avatar
    wdx223_Daniel
    3 years ago

    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's avatar
    wdx223_Daniel
    3 years ago
    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') )
    )
)
wdx223_Daniel's avatar
wdx223_Daniel
Icon for Community Champion rankCommunity Champion
3 years ago

 

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's avatar
Sofinobi
Icon for Helper IV rankHelper IV
3 years ago

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's avatar
    wdx223_Daniel
    Icon for Community Champion rankCommunity Champion
    3 years ago
    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's avatar
      Sofinobi
      Icon for Helper IV rankHelper IV
      3 years ago

      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's avatar
        wdx223_Daniel
        Icon for Community Champion rankCommunity Champion
        3 years ago
        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') )
    )
)