Sumx not working with summarize

[sammy02hk]

Hi there,

I try to use sumx with summarize to group the article_code. However its not work as well. Which is work in my other bi report.

I would like to sum Sales by ArticleCode within the Period StartDate2 to +6. Thank you very much....

LW Sales 1 =

SUMX(
SUMMARIZE(

'Full 100013','Full 100013'[Article_Code],'Full 100013'[Billing_Date]),

Var StartDate = LOOKUPVALUE(DATA_LIST_CUR_DateMatch[LW],DATA_LIST_CUR_DateMatch[Date],

SELECTEDVALUE(DATA_LIST_CUR_DateMatch[WkMonday]))

Var StartDate2 = DATE(LEFT(StartDate,4),MID(StartDate,6,2),RIGHT(StartDate,2))

return

calculate(

SUMX('Full 100013','Full 100013'[Sales]),

filter(all('Full 100013'),'Full 100013'[Billing_Date]>= StartDate2 &&

'Full 100013'[Billing_Date]<= StartDate2 +6 )))

Thank you very much....    

[sammy02hk]

Hi Yolo,

Thank you for your help ...

Sammy 

[sammy02hk]

I have try different way to write the syntax but it is not working at all. The no is correct, just cant group by with summarize.

Thank you for your help....

[v-xinruzhu-msft · Accepted solution]

Hi @sammy02hk 

Maybe you can try the following code

LW Sales 1 =
Var StartDate = LOOKUPVALUE(DATA_LIST_CUR_DateMatch[LW],DATA_LIST_CUR_DateMatch[Date],
SELECTEDVALUE(DATA_LIST_CUR_DateMatch[WkMonday]))
Var StartDate2 = DATE(LEFT(StartDate,4),MID(StartDate,6,2),RIGHT(StartDate,2))

return
SUMX(filter(all('Full 100013'),'Full 100013'[Article_Code]=Max('Full 100013'[Article_Code])&&'Full 100013'[Billing_Date]>= StartDate2 &&
'Full 100013'[Billing_Date]<= StartDate2 +6 ),[Sales])

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.