Sum values between multiple periods

[Daniel_Gatti]

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]

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
    )

[Daniel_Gatti]

Hello @tamerj1 

I didn't worked. The result is still showing 3500, when it should be 1500, because ADRIANA C was not active between 01/02/2022 till 28/02/2022

Here's the link for the file:

https://drive.google.com/file/d/1_HGHM9_f2qkyVGRtQZSVN3WmF_tbakIj/view?usp=sharing

[tamerj1 · Accepted solution]

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]
            )
        )
    )
)

[Daniel_Gatti]

@tamerj1 

thank you so much!😀