Forum Discussion

crhisbergas's avatar
crhisbergas
Regular Visitor
3 years ago
Solved

Column with SUMX and filter by dates and ids

Hi,

Im working to create a calculated column with the average sales of last 12 months.

But I don't know how to filter the last 12 months, I've tried in many ways but Powerbi alwys returns error. Can someone help me on this.

 

Sales_12Mth =
SUMX(
    FILTER(
    Sales,
          Sales[Cod_Cliente] = Clients[ID_Cliente] &&
          Sales[Cod_Prod] = Sales_Clients[ID_Produto]),
     Sales[Quantidade]
)
Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    3 years ago

    Hi crhisbergas ,

    Tweak the formula like this:

    Sales_12Mth =
VAR __Today =
    TODAY ()
VAR __12MonthsAgo =
    EOMONTH ( __Today, -12 )
VAR __Begin =
    DATE ( YEAR ( __12MonthsAgo ), MONTH ( __12MonthsAgo ), DAY ( __Today ) )
RETURN
    SUMX (
        FILTER (
            Sales,
            Sales[Cod_Cliente] = Clients[ID_Cliente]
                && Sales[Cod_Prod] = Sales_Clients[ID_Produto]
                && Sales[Date] >= __Begin
                && Sales[Date] <= __Today
        ),
        Sales[Quantidade]
    )

    Best Regards,
    Community Support Team _ kalyj

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

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    3 years ago

    Hi crhisbergas ,

    Is your problem solved? If so, would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirment will find the solution quickly and benefit here, thank you!

     

    Best Regards,
    Community Support Team _ kalyj

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    3 years ago

    crhisbergas Try:

    Sales_12Mth =
  VAR __Today = TODAY()
  VAR __12MonthsAgo = EOMONTH(__Today, -12)
  VAR __Begin = DATE(YEAR(__12MonthsAgo), MONTH(__12MonthsAgo), DAY(__Today))
  VAR __Result = 
SUMX(
    FILTER(
    Sales,
          Sales[Cod_Cliente] = Clients[ID_Cliente] &&
          Sales[Cod_Prod] = Sales_Clients[ID_Produto]) &&
          Sales[Date] >= __Begin && Sales[Date] <= __Today,
     Sales[Quantidade]
)
    • crhisbergas's avatar
      crhisbergas
      Regular Visitor
      3 years ago

      Hi Greg,

      When I tried to run its showing the message.

       

      The syntax )' it's incorrect. --- Error message:

      A sintaxe de ')' está incorreta. (DAX( VAR __Today = TODAY() VAR __12MonthsAgo = EOMONTH(__Today, -12) VAR __Begin = DATE(YEAR(__12MonthsAgo), MONTH(__12MonthsAgo), DAY(__Today)) VAR __Result = SUMX( FILTER( Sales, Sales[Cod_Cliente] = Clients[ID_Cliente] && Sales[Cod_Prod] = Sales_Clients[ID_Produto] && (Sales[Date] >= __Begin && Sales[Date] <= __Today), Sales[Quantidade])))).

       

       

      Sales_12Mth =
        VAR __Today = TODAY()
        VAR __12MonthsAgo = EOMONTH(__Today, -12)
        VAR __Begin = DATE(YEAR(__12MonthsAgo), MONTH(__12MonthsAgo), DAY(__Today))
        VAR __Result =
      SUMX(
          FILTER(
          Sales,
                Sales[Cod_Cliente] = Clients[ID_Cliente] &&
                Sales[Cod_Prod] = Sales_Clients[ID_Produto] &&
                (Sales[Date] >= __Begin && Sales[Date] <= __Today),
           Sales[Quantidade]
      )
      • v-yanjiang-msft's avatar
        v-yanjiang-msft
        Icon for Community Support rankCommunity Support
        3 years ago

        Hi crhisbergas ,

        Tweak the formula like this:

        Sales_12Mth =
VAR __Today =
    TODAY ()
VAR __12MonthsAgo =
    EOMONTH ( __Today, -12 )
VAR __Begin =
    DATE ( YEAR ( __12MonthsAgo ), MONTH ( __12MonthsAgo ), DAY ( __Today ) )
RETURN
    SUMX (
        FILTER (
            Sales,
            Sales[Cod_Cliente] = Clients[ID_Cliente]
                && Sales[Cod_Prod] = Sales_Clients[ID_Produto]
                && Sales[Date] >= __Begin
                && Sales[Date] <= __Today
        ),
        Sales[Quantidade]
    )

        Best Regards,
        Community Support Team _ kalyj

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

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi crhisbergas ,

    Is your problem solved? If so, would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirment will find the solution quickly and benefit here, thank you!

     

    Best Regards,
    Community Support Team _ kalyj

    • crhisbergas's avatar
      crhisbergas
      Regular Visitor
      3 years ago

      Hi All,

      with your support I can finish the job.

       

      thanks a lot!!!