Forum Discussion

sammy02hk's avatar
sammy02hk
Icon for Helper I rankHelper I
3 years ago
Solved

Sumx not working with summarize

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

    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.

     

sammy02hk's avatar
sammy02hk
Icon for Helper I rankHelper I
3 years ago

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....

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.