topic Re: sum only when date is present in both tables in DAX Commands and Tips

sum only when date is present in both tables

Anonymous — Tue, 04 Jul 2023 08:14:03 GMT

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 ?

Re: sum only when date is present in both tables

johnt75 — Tue, 04 Jul 2023 10:26:18 GMT

Try

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

Re: sum only when date is present in both tables

Anonymous — Tue, 04 Jul 2023 10:51:12 GMT

hi, johnt75 ,

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

Re: sum only when date is present in both tables

johnt75 — Tue, 04 Jul 2023 12:40:09 GMT

How about

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

Re: sum only when date is present in both tables

Anonymous — Tue, 04 Jul 2023 13:54:26 GMT

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 ?

Re: sum only when date is present in both tables

johnt75 — Tue, 04 Jul 2023 14:04:45 GMT

Yes, you can use

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

Re: sum only when date is present in both tables

Anonymous — Tue, 04 Jul 2023 14:12:43 GMT

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

Re: sum only when date is present in both tables

Anonymous — Tue, 04 Jul 2023 14:14:37 GMT

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

Re: sum only when date is present in both tables

johnt75 — Tue, 04 Jul 2023 14:17:49 GMT

What does your model look like ? I tried it with

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

Re: sum only when date is present in both tables

Anonymous — Tue, 04 Jul 2023 14:20:51 GMT

it seems to be the sames

Re: sum only when date is present in both tables

johnt75 — Tue, 04 Jul 2023 14:34:14 GMT

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.

Re: sum only when date is present in both tables

Anonymous — Tue, 04 Jul 2023 14:39:18 GMT

this is when filter applied to the matrix

and this is when not

Re: sum only when date is present in both tables

johnt75 — Tue, 04 Jul 2023 14:57:04 GMT

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

Re: sum only when date is present in both tables

Anonymous — Tue, 04 Jul 2023 15:01:29 GMT

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

Re: sum only when date is present in both tables

Anonymous — Tue, 04 Jul 2023 15:05:57 GMT

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

Re: sum only when date is present in both tables

johnt75 — Tue, 04 Jul 2023 15:10:12 GMT

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.

Re: sum only when date is present in both tables

johnt75 — Tue, 04 Jul 2023 15:18:07 GMT

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

Re: sum only when date is present in both tables

Anonymous — Tue, 04 Jul 2023 15:23:32 GMT

somehow it returns the same

Re: sum only when date is present in both tables

johnt75 — Tue, 04 Jul 2023 15:29:10 GMT

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

Re: sum only when date is present in both tables

Anonymous — Tue, 04 Jul 2023 15:33:49 GMT

in excel total is correct