Forum Discussion

Daniel_Gatti's avatar
Daniel_Gatti
Regular Visitor
4 years ago
Solved

Sum values between multiple periods

Hello,

I have the following model in my Power BI report:model

My first measure is a simple sum:

01 Total Sale = SUM(fSales[Sale])

 

What I'm trying to achieve is a measure that calculates the sum of Sales when the architect is active for a vendor. So I created the measure: 99 Total Sale Active Architect

 

99 Total Sale Active Architect =
VAR Carteira_Arquitetos = VALUES(Carteira[Cod Architect])
VAR Carteira_inicio = min(Carteira[Start date])
VAR Carteira_Fim = max(Carteira[end date])

RETURN

CALCULATE(
     Medidas[01 Total Sale],
     FILTER(
          fSales,
          fSales[Cod Architect] IN Carteira_Arquitetos &&
          (fSales[Data] > Carteira_inicio &&
          fSales[Data] < Carteira_Fim)
          )
)

 

Wich was working fine until a same architect has more than one active period like this:

 

Now my measure is returning the wrong value:

Thanks in advance

  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi Daniel_Gatti 
    Here is the sample file with the solution https://we.tl/t-wcDtQlG6IL

    99 Total Sale Active Architect = 
CALCULATE (
    Medidas[01 Total Venda],
    FILTER (
        fSales,
        fSales[Cod Architect] IN
        DISTINCT (
            SELECTCOLUMNS (
                FILTER (
                    Carteira,
                    Carteira[Start date] <= fSales[Data]
                        && Carteira[End date] >= fSales[Data]
                ),
                "@CodArchitect", Carteira[Cod Architect]
            )
        )
    )
)

4 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Hi Daniel_Gatti 
    Please try

    99 Total Sale Active Architect =
VAR CurrentDate =
    MAX ( dCalendar[Date] )
VAR Carteira_Arquitetos =
    DISTINCT (
        SELECTCOLUMNS (
            FILTER (
                Carteira,
                Carteira[Start date] <= CurrentDate
                    && Carteira[End date] <= CurrentDate
            ),
            "@CodArchitect", Carteira[Cod Architect]
        )
    )
RETURN
    CALCULATE (
        Medidas[01 Total Sale],
        fSales[Cod Architect] IN Carteira_Arquitetos
    )