cancel
Showing results for
Did you mean:
Frequent Visitor

## CALCULATE WITH TWO FILTERS

Hi!!

I have a ledger table and I want to calculate the sum of all the transactions. I used CALCULATE, filtering by ALL Calendari because I want the acumulated value.

Valor immoble =
VAR maxF = MAX(Calendari[Date])
RETURN
CALCULATE(SUM(LEDGERTRANS_Immobles[Import]), FILTER(ALL(Calendari), Calendari[Date] <= maxF))

But also, I need filter my ledger table because I don't want to sum the registers that the sell-date is less than the selected date:
Valor immoble net =
VAR maxF = MAX(Calendari[Date])
RETURN

CALCULATE(SUM(LEDGERTRANS_Immobles[Import]), FILTER(ALL(Calendari), Calendari[Date] <= maxF), FILTER(LEDGERTRANS_Immobles, LEDGERTRANS_Immobles[Data_Venda]>maxF))

My problem is that with Valor Immoble measure I obtain the sum of all transactions (including the selled features). If I use the measure Valor immoble net, I only obtain the sum of the transactions in the current year.

How can I mix them?

1 ACCEPTED SOLUTION
Super User II

Hi @Jeanetted  - sorry I wasn't able to get to this until now.  Can you confirm that the following results are what is expected?

Year = 2020

Year = 2019

Year = 2018

Year = 2017

Year = 2016

If it is, here is the code for you to use

Valor immoble net =
VAR maxF =
MAX ( Calendari[Date] )
RETURN
CALCULATE (
SUM ( LEDGERTRANS_Immobles[Import] ),
FILTER (
ALLEXCEPT( LEDGERTRANS_Immobles, LEDGERTRANS_Immobles[Promoció] ),
LEDGERTRANS_Immobles[Data_Venda] > maxF ),
FILTER ( ALL ( Calendari ), Calendari[Date] <= maxF )
)

David

Proud to be a Super User!

14 REPLIES 14
Super User II

Hi @Jeanetted - looks like you need to remove the filters from LEDGERTRANS_Immobles to counteract the slicer. You can use ALL for that just like with the Calendari table.

Valor immoble net =
VAR maxF =
MAX ( Calendari[Date] )
RETURN
CALCULATE (
SUM ( LEDGERTRANS_Immobles[Import] ),
FILTER ( ALL ( Calendari ), Calendari[Date] <= maxF ),
FILTER ( ALL ( LEDGERTRANS_Immobles ), LEDGERTRANS_Immobles[Data_Venda] > maxF )
)

Hope this helps

David

Proud to be a Super User!

Frequent Visitor

Thanks David, but the solution don't work. The relationship between LEDGERTRANS_Immobles and Calendari is another field:

Calendari[Date]----------LEDGERTRANS_Immobles[Data]

I need use the secondary relashionship:

Calendari[Date]----------LEDGERTRANS_Immobles[Data_Venda]

But I don't know hao to use the function (USERELATIONSHIP) into a FILTER Function.

(Data, not Data_Venda). I think I need use the secondary relationship

Super User II

Hi @Jeanetted -

USERELATIONSHIP is a CALCULATE modifier so you're really close.  The code should look something like

Valor immoble net =
VAR maxF =
MAX ( Calendari[Date] )
RETURN
CALCULATE (
SUM ( LEDGERTRANS_Immobles[Import] ),
FILTER ( ALL ( Calendari ), Calendari[Date] <= maxF ),
USERELATIONSHIP ( LEDGERTRANS_Immobles[Data_Venda], Calendari[Date] )
)

This assumes that you currently have the relationship in your model, just deactivated.

If that still doesn't work, can you share a copy of your report file with sensitive data removed?

David

Proud to be a Super User!

Frequent Visitor

It was a good idea, but also it does'nt work.

I'll show you the data.

I want sum the field Import acumulated since a selected data from a calendar, but only id Data_Venda > SELECTED date.

In this example, I don't want to sum the registers from LOCAL CAMBRILS.

I need filter the Calendari Table because I need to sum the acumulated registers of the field Import.

And I need filter the table show below (LEDGERTRANS_immobles) if Data_Venda > Selected Data.

But the relationship between Calendari and LEDGERTRANS_immobles is the field Data, not Data_Venda.

Super User II

Let me see if I understand the scenario.

You need the sum of a value on LEDGERTRANS_immobles where Date is less than the max date, but Date_Venda is greater than the max date?

Please confirm that this is the scenario (your pictures didn't come through if there were any in your last post).

Proud to be a Super User!

Frequent Visitor

Yes, is this!!

Frequent Visitor

Super User II

I'm suprised that my first set of code doesn't work. Maybe try this (which is just reversing the filters, which shouldn't make a difference but you never know).

Valor immoble net =
VAR maxF =
MAX ( Calendari[Date] )
RETURN
CALCULATE (
SUM ( LEDGERTRANS_Immobles[Import] ),
FILTER ( ALL ( LEDGERTRANS_Immobles ), LEDGERTRANS_Immobles[Data_Venda] > maxF ),
FILTER ( ALL ( Calendari ), Calendari[Date] <= maxF )
)

If that doesn't work, can you provide a copy of your report with sensitive data removed? There may be other filters at play that are preventing you from getting the answers you want.

Proud to be a Super User!

Frequent Visitor

Hello dedelman,

I prepared a little copy. How can I send you?

Super User II

Share it as a link via OneDrive, Dropbox, etc

Proud to be a Super User!

Frequent Visitor
Super User II

I cannot access that link since I am not in your organization

Proud to be a Super User!

Frequent Visitor
Super User II

Hi @Jeanetted  - sorry I wasn't able to get to this until now.  Can you confirm that the following results are what is expected?

Year = 2020

Year = 2019

Year = 2018

Year = 2017

Year = 2016

If it is, here is the code for you to use

Valor immoble net =
VAR maxF =
MAX ( Calendari[Date] )
RETURN
CALCULATE (
SUM ( LEDGERTRANS_Immobles[Import] ),
FILTER (
ALLEXCEPT( LEDGERTRANS_Immobles, LEDGERTRANS_Immobles[Promoció] ),
LEDGERTRANS_Immobles[Data_Venda] > maxF ),
FILTER ( ALL ( Calendari ), Calendari[Date] <= maxF )
)

David

Proud to be a Super User!

Announcements

#### Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks