sum only when date is present in both tables

[Anonymous]

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]

Try

Total Cost =
CALCULATE (
    [cost] + [rent],
    CROSSFILTER ( 'Date'[Date], 'cost table'[Date], BOTH )
)

[Anonymous]

hi, @johnt75 ,

it returns the same value 550 , which includes rent for Feb ☹️

[johnt75]

How about

Total Cost =
CALCULATE (
    [cost] + [rent],
    TREATAS ( VALUES ( 'cost table'[Date] ), 'Date'[Date] )
)

[Anonymous]

this one works, but only when I apply Acc[Code] = "160" to a visual, I tried to add it into Calculate as filter, it didn't help, only helps when it's applied to the matrix. 
is it possible to insert it into the measure ?

[johnt75]

Yes, you can use

Total Cost = CALCULATE (
	[Costs] + [Rents],
	'Acc'[Code] = "160",
    TREATAS ( VALUES ( 'Costs'[Date] ), 'Date'[Date] )
)

[Anonymous]

but if i apply it to the visual it does work properly

[johnt75]

What does your model look like ? I tried it with

and it worked. Also, at what level of the hierarchy is the account code ?

[Anonymous]

it seems to be the sames

[johnt75]

Try putting a couple more measures into your matrix,

Dates with acc =
CALCULATE (
    COUNTROWS ( 'Calendar' ),
    'Acc'[Code] = "160",
    TREATAS ( VALUES ( 'Cost'[Date] ), 'Calendar'[Date] )
)

Dates without acc =
CALCULATE (
    COUNTROWS ( 'Calendar' ),
    TREATAS ( VALUES ( 'Cost'[Date] ), 'Calendar'[Date] )
)

See what they give both with and without a filter on the visual.

[Anonymous]

this is when filter applied to the matrix

and this is when not

[johnt75]

It looks like its completely ignoring the filter inside the CALCULATE. Not sure if this will make any difference but try

Total Cost =
VAR MonthsWithCost =
    CALCULATETABLE ( VALUES ( 'Costs'[Date] ), 'Acc'[code] = "160" )
VAR Result =
    CALCULATE (
        [Costs] + [Rents],
        'Acc'[Code] = "160",
        TREATAS ( MonthsWithCost, 'Date'[Date] )
    )
RETURN
    Result

[Anonymous]

it calculated fine, but I noticed that totals didn't change
how could this be ?

[johnt75 · Accepted solution]

Try this version

Total costs =
CALCULATE (
    SUMX (
        DISTINCT ( Costs[Date] ),
        VAR CurrentDate = Costs[Date]
        RETURN
            CALCULATE ( [Costs] + [Rents], TREATAS ( { CurrentDate }, 'Calendar'[Date] ) )
    ),
    'Acc'[Code] = "160"
)

[Anonymous]

somehow it returns the same

[johnt75]

Its possible that the total is actually correct. Export the data to Excel and see what it comes up with as the total.

[Anonymous]

in excel total is correct

[johnt75]

Check your costs table for entries with a date but either blank or 0 cost. 

[Anonymous]

doesn't have any blanks or zeros

[johnt75]

I meant where the cost was either blank or 0, not the date.

Is it possible to share a PBIX with any confidential info either removed or anonymised ?

[Anonymous]

Yesterday I spent the whole evening deleting confidential data so that I could share the file, it turns out that without this confidential information it works fine, so I guess the problem is in the source data.

p.s.
I deleted all the blanks and zeros, double-checked everything, it didn't help.
Perhaps you have other suggestions?

btw, accepted your solution, thanks a lot for help 🙂