topic Re: Sum values between multiple periods in DAX Commands and Tips

Sum values between multiple periods

Daniel_Gatti — Mon, 11 Jul 2022 19:03:56 GMT

Hello,

I have the following model in my Power BI report:

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

Re: Sum values between multiple periods

tamerj1 — Mon, 11 Jul 2022 20:08:41 GMT

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 )

Re: Sum values between multiple periods

Daniel_Gatti — Tue, 12 Jul 2022 00:22:14 GMT

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

Re: Sum values between multiple periods

tamerj1 — Tue, 12 Jul 2022 04:18:15 GMT

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

Re: Sum values between multiple periods

Daniel_Gatti — Tue, 12 Jul 2022 15:22:00 GMT

tamerj1

thank you so much!😀