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

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

     

3 Replies

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