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, below is how I am able to create a new table with sold-to name and rolling prior 12 month sales only. And this is sucessful.

 

SummaryTable = SUMMARIZE(
                    FILTER(
                    EDW_Table,
                    EDW_Table[DerivedInvoiceDate] > EDATE(TODAY(), -12) && EDW_Table[DerivedInvoiceDate] <= EDATE(TODAY(), -0)
                ),
    EDW_Table[SoldToName],
    "L12M AUD",
    CALCULATE([Total Order Amount AUD],LEFT(EDW_Table[OrderType],3) = "ZTA")
)
 
But, how can I create a table with sold-to name, and both rolling 12 months and rolling 24-12 months. The rolling 24-12 months calculation is below:
 
SummaryTable = SUMMARIZE(
                    FILTER(
                    EDW_Table,
                    EDW_Table[DerivedInvoiceDate] > EDATE(TODAY(), -24) && EDW_Table[DerivedInvoiceDate] <= EDATE(TODAY(), -12)
                ),
    EDW_Table[SoldToName],
    "L12M AUD",
    CALCULATE([Total Order Amount AUD],LEFT(EDW_Table[OrderType],3) = "ZTA")
)
 
Thank you so much!
ChiragGarg2512 I saw your solution to create an aggregate table with one criteria, is it possible to have two?
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) ))
    )

2 Replies

  • amitchandak's avatar
    amitchandak
    Super 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) ))
    )