Forum Discussion

Wei_Pan's avatar
Wei_Pan
Frequent Visitor
1 year ago
Solved

How to create an aggregate table from main table with multiple columns & criteria

Hi I want to create a second table on aggregated level containing sold-to name, rolling prior 12 months sales, rolling prior 24-12 months sales only. Based on some earlier post in the community, belo...
Need Help
  • amitchandak's avatar
    amitchandak
    1 year ago

    Wei_Pan , Try like

     

    SUMMARIZE(EDW_Table,
    "L12M AUD",CALCULATE([Total Order Amount AUD],filter(EDW_Table, LEFT(EDW_Table[OrderType],3) = "ZTA" && EDW_Table[DerivedInvoiceDate] > EDATE(TODAY(), -12) && EDW_Table[DerivedInvoiceDate] <= EDATE(TODAY(), -0) ))
    ,
    "L12M AUD",CALCULATE([Total Order Amount AUD],filter(EDW_Table, LEFT(EDW_Table[OrderType],3) = "ZTA" && EDW_Table[DerivedInvoiceDate] > EDATE(TODAY(), -24) && EDW_Table[DerivedInvoiceDate] <= EDATE(TODAY(), -12) ))
    )

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
1 year ago

Wei_Pan , Try like

 

SUMMARIZE(EDW_Table,
"L12M AUD",CALCULATE([Total Order Amount AUD],filter(EDW_Table, LEFT(EDW_Table[OrderType],3) = "ZTA" && EDW_Table[DerivedInvoiceDate] > EDATE(TODAY(), -12) && EDW_Table[DerivedInvoiceDate] <= EDATE(TODAY(), -0) ))
,
"L12M AUD",CALCULATE([Total Order Amount AUD],filter(EDW_Table, LEFT(EDW_Table[OrderType],3) = "ZTA" && EDW_Table[DerivedInvoiceDate] > EDATE(TODAY(), -24) && EDW_Table[DerivedInvoiceDate] <= EDATE(TODAY(), -12) ))
)

Wei_Pan's avatar
Wei_Pan
Frequent Visitor
1 year ago

Thank you amitchandak . The expression worked well for me.