Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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
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
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
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
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.
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).
Yes, is this!!
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.
Hello dedelman,
I prepared a little copy. How can I send you?
Share it as a link via OneDrive, Dropbox, etc
I cannot access that link since I am not in your organization
I'm sorry. I think know you'll have permissionhttps://vallcompanys-my.sharepoint.com/:u:/g/personal/jeanetted_binera_es/EW6rMxSqRtVMrcq36Js61PoBCO...
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |