sum only when date is present in both tables

[Ali_Shakh]

hello everyone!

I have the following problem, I can't figure out how to solve it.

I have 2 tables Cost and Rent, both linked via a calendar table

I need to sum the values from Cost and Rent only when they are present in the Cost table, otherwise 0,

a simple calculation works fine for single month selected,

Total Cost =
CALCULATE(

        [cost],

        Acc[Code] = "160")

    +

    CALCULATE(

        [rent],

        Acc[Code] = "160")

but when I select YTD (or deselect the all months, only the year is selected), it returns me an incorrect value (summing the total cost and rent),
is there a way to include the values in the total cost only when [cost] is present from the beginning of the year

Example:

 Cost table

Date	Cost
01-01-2023	100
01-03-2023	150

Rent table

Date	Rent
01-01-2023	50
01-02-2023	100
01-03-2023	150

right now it returns me YTD = 550, 
(Jan, 23 = Cost + Rent => 100+50 = 150
Feb, 23 = Cost + Rent => 0+100 = 100 (Cost is not present)
Mar, 23 = Cost + Rent => 150+150 = 300)

but it should return me:
Total Cost = 450
(Jan, 23 = Cost + Rent => 100+50 = 150
Feb, 23 = 0 (Cost is not present)
Mar, 23 = Cost + Rent => 150+150 = 300)

and it should work with a single month as well

is that possible to achieve ?

[johnt75]

From the behaviour we have seen I would guess that there is some rogue data in Costs which is causing a date from that table to be picked up when it shouldn't be.

If you identify a particular period when the data is wrong then examine all the Costs data for that period, regardless of other filters. That might shed some light on where the issue is.

[Ali_Shakh]

Good news,
I managed to get the correct value using SUMX(ValuesGEO[City], your code).

Also thanks to Alberto Ferrari and his video, I figured out that I need to iterate on city level to get what I wanted 😁
https://www.youtube.com/watch?v=6rgAkejrup8&ab_channel=SQLBI 

[johnt75]

Their videos are always must-watch.

Glad you solved it.

[Ali_Shakh]

it worked!
thanks a lot, do you by any chance know what was the issue ? why simple filter in calculate didn't work ?

[johnt75]

I'm nowhere near sure on this, but I think its to do with the way that queries are executed to produce a matrix, particularly a matrix with a hierarchy on the roles. Several queries are produced and then combined, and my guess is that at some level the filter we were generating resulted in an empty table, or a blank value, and that led to its being ignored for some parts of the calculation.

When you had the filter on the visual then that would be applied to all of the different queries and that's why it worked.

[Ali_Shakh]

i tried it previously, for some reason it doesn't help