Total isn't adding up correctly if I add a filter to the visual

[SLucky]

Hello,

I have three tables.

Property Info table has property_id and R_Date. R_Date is the property active date in the system.

Financial table has revenue and transaction_date. I created a calculated column called Check = if(transaction_date >= R_Date, 1, 0) which limits the transaction_date to be the valid date. 

Third table is a Calendar table.

The relationship between Property Info and Financial is property_id 1 to many. Financial table with calendar table is linked by transaction_date and date column in Calendar table. No relationship between Property Info and Calendar table. 

I would like to create a measure to show the properties same time last year revenue when 1. if I add Check column as the filter to the visual, and filter it as Check = 1, when selected calendar date >= R_Date it returns last year revenue, otherwise returns blank or zero. 2. If I remove Check filter, it shows last year revenue for all date.

To make it simple, below property R_Date is 10/2/2023. I selected 10/1/2023 - 10/4/2023 in calendar table. [CYA - Total Revenue] is a sum(revenue) measure. No "Check =1" filter looks good as it shows last year value. With "Check =1" filter is incorrect as October total isn't adding up correctly. 

Summary, this measure works for each individual date but not by month or total if I add Check = 1 to the visual. How can I show the total value correctly even with the filter? Thanks!

No "Check =1" filter: looks good

With "Check=1" filter: 10/1 is gone which is good while October total is not adding up correctly(has sum up Oct 1st original value)

[ray_codex]

The first thing I notice, you are referring to selectedvalue('Calendar table'[date]]). This returns a single value if there is one or blank. With the month oktober you have 31 values, so there is no single value, returning blank, same goes for totals. Totals will return every date in the filtered range.

• I think your issue starts there. You can try something like using HASONEVALUE and create different calculations on True or False, or alter the measure completely. 

[SLucky]

Thanks! I change it to Max(Calendar date) and the monthly and total come up but the number isn't correct with Check =1 filter on.