Forum Discussion

gizattos's avatar
gizattos
Regular Visitor
6 years ago
Solved

Sum by weeknumber

Hi guys,

 

I'm trying to create a column based on last week value with DAX, I already create weeknumber column. I tryed this code :

lastweek value = CALCULATE(SUMX(Tabela,Tabela[Insight]),FILTER(ALL(Tabela),Tabela[weeknumber]-1))

But the column shows the sum of all table, and I want something like this:

 

insightweeknumberlastweek value
1103
1103
1103
191
191
191
180
080

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi gizattos ,

     

    You can use EARLIER() function

    Column = CALCULATE(SUM('Table'[insight]),FILTER('Table','Table'[weeknumber]=EARLIER('Table'[weeknumber])-1))

     

     

    Best Regards,

    Jay

     

    Community Support Team _ Jay Wang

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

9 Replies

  • Tahreem24's avatar
    Tahreem24
    Icon for Super User rankSuper User
    6 years ago

    gizattos ,

    Try the below DAX Measure:

    Measure = CALCULATE(SUM(Sheet12[Insight]),FILTER(ALL(Sheet12),Sheet12[Weeknum]=MAX(Sheet12[Weeknum])-1))
     
  • Tahreem24's avatar
    Tahreem24
    Icon for Super User rankSuper User
    6 years ago

    gizattos ,

    You can also try this formula also:

    Measure = SUMX(FILTER(ALL(Sheet12),Sheet12[Weeknum]=MAX(Sheet12[Weeknum])-1),Sheet12[Insight])
     
  • Tahreem24's avatar
    Tahreem24
    Icon for Super User rankSuper User
    6 years ago

    gizattos ,

    You can also try this formula also:

    Measure = SUMX(FILTER(ALL(Table),Sheet12[Weeknum]=MAX(Table[Weeknum])-1),Table[Insight])
     
     
     
      • Tahreem24's avatar
        Tahreem24
        Icon for Super User rankSuper User
        6 years ago

        gizattos ,

        Try the below COlumn DAX:

        Column = CALCULATE(SUM(Table[Insight]),FILTER(ALL(Table),Table[Weeknum]=MAX(Table[Weeknum])-1),Table[Insight])
         
         
  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    6 years ago

    gizattos 

     

    Please try the DAX below to see if this works for you

     

    lastweek value = 

VAR d = max(tabela[weeknumber])
RETURN CALCULATE(SUMX(Tabela,Tabela[Insight]),FILTER(ALL(Tabela),Tabela[weeknumber]=d-1))

     

     

     

     

    • gizattos's avatar
      gizattos
      Regular Visitor
      6 years ago

      didn't work, now i have the sum of a specific week repeated for all rows.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi gizattos ,

     

    You can use EARLIER() function

    Column = CALCULATE(SUM('Table'[insight]),FILTER('Table','Table'[weeknumber]=EARLIER('Table'[weeknumber])-1))

     

     

    Best Regards,

    Jay

     

    Community Support Team _ Jay Wang

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