Need Measure to Partially Ignore Slicer Filter

[KayceVC]

I am having an issue with a measure where I need it to respect the upper limit based on a slicer, but not the lower.

The tables involved are Months, Dates, Projects and Time Entries. Months and Dates are 1:*. Dates is 1:* on Projects and Time Entries. Projects is 1:* on Time Entries. 

What I need to display in my table is the total amount of Billable_Amt from Time Entries for the period selected in the slicer and the total amount of Billable_Amt from Time Entries up to the MAX date in Time Entries. The first part of the need I have already written and works with no issue. The issue is getting the oldest date available in my Dates table (ignoring the slicer selection) while keeping the newest date available based on the slicer selection.

The current measure (not working) is: 

test4 = 
VAR MinDate = CALCULATE(MIN(Dates[Date]), REMOVEFILTERS())
RETURN
CALCULATE(SUM('Time Entries'[Billable_Amt]),'Time Entries'[Included In Billable Hours], FILTER('Time Entries', 'Time Entries'[Date] >= MinDate))

I thought adding the REMOVEFILTERS() to the MIN would accomplish what I need, but the amount returned is the same as the measure without the FILTER in the CALCULATION. Regardless of what filters, if any, are chosen, the measures continue to return the same value.

The data I am currently working against is:

Month: Aug 2020

Max Filtered Date: 8/31/2020

Min Filtered Date: 8/1/2020

Min Date in Table: 2/23/2019

Total Billable_Amt in Aug 2020: 17,746.00

Total Billable_Amt through Aug 2020: 76,113.50.

I did test the VAR Min Date from above in a separate measure and it is returning the value expected of 2/23/2019, so I am uncertain why it's not working.

[KayceVC · Accepted solution]

After working on this off and on for a few days and doing a fair bit of testing, I was able to come with a solution that works. Thank you to everyone who offered suggestions. Working measure is below.

test4 = 
VAR MaxDate = MAX(Dates[Date])
VAR MinDate = MIN(Dates[Date])
Var MaxTime = CALCULATE(SUM('Time Entries'[Billable_Amt]),'Time Entries'[Included In Billable Hours], ALL(Dates))
Var PeriodTime = CALCULATE(SUM('Time Entries'[Billable_Amt]),'Time Entries'[Included In Billable Hours], FILTER(Dates, Dates[Date] <= MaxDate && Dates[Date] >= MinDate))
Var ThroughPeriodTime = CALCULATE(SUM('Time Entries'[Billable_Amt]),'Time Entries'[Included In Billable Hours], FILTER(ALL(Dates), Dates[Date] <= MaxDate))
Var SincePeriodTime = CALCULATE(SUM('Time Entries'[Billable_Amt]),'Time Entries'[Included In Billable Hours], FILTER(ALL(Dates), Dates[Date] > MaxDate))
Var BeforePeriodTime = CALCULATE(SUM('Time Entries'[Billable_Amt]),'Time Entries'[Included In Billable Hours], FILTER(ALL(Dates), Dates[Date] < MinDate))
VAR BillingAmount = SUM(Projects[Billing_Amount])
RETURN
IF(ThroughPeriodTime < SUM(Projects[Billing_Amount]) && (HASONEFILTER(Dates[Week]) || HASONEFILTER(Dates[Month])), PeriodTime, 
    IF(BeforePeriodTime < SUM(Projects[Billing_Amount]) && (HASONEFILTER(Dates[Week]) || HASONEFILTER(Dates[Month])), BillingAmount-BeforePeriodTime, 
        IF(ThroughPeriodTime < SUM(Projects[Billing_Amount]), PeriodTime,
            IF(BeforePeriodTime < SUM(Projects[Billing_Amount]), BillingAmount-BeforePeriodTime,
                IF(BeforePeriodTime > SUM(Projects[Billing_Amount]) && (HASONEFILTER(Dates[Week]) || HASONEFILTER(Dates[Month])), 0,
                    IF(BeforePeriodTime > SUM(Projects[Billing_Amount]), BillingAmount,0
                    )
                )
            )
        )
    )    
)

[V-pazhen-msft]

@KayceVC 

A bit confusing, but to make slicer working. Shouldn't you use allselected(dates) instead of all(dates) in the filter expression.

Paul

[KayceVC]

No, I finally got this it to work last night with a combination of FILTER, ALL and using VAR/RETURN. I will post the solution shortly in case anyone else should have a similiar issue. I do thank you for your suggestion though. This issue was quite confusing and I did have issues trying to fully explain it.

[KayceVC]

I feel like I am getting closer to the solution, but I am not quite there yet. The new measure I am working with now is: 

test4 = 
VAR MaxDate = MAX(Dates[Date])
VAR MinDate = MIN(Dates[Date])
VAR EarliestDate = CALCULATE(MIN(Dates[Date]), REMOVEFILTERS())
Var MaxTime = CALCULATE(SUM('Time Entries'[Billable_Amt]),'Time Entries'[Included In Billable Hours], ALL(Dates))
Var PeriodTime = CALCULATE(SUM('Time Entries'[Billable_Amt]),'Time Entries'[Included In Billable Hours], FILTER(Dates, Dates[Week] <= MaxDate && Dates[Week] >= MinDate))
Var AllCurrentTime = CALCULATE(SUM('Time Entries'[Billable_Amt]),'Time Entries'[Included In Billable Hours], ALL(Dates), FILTER(Dates, Dates[Week] <= MaxDate))
RETURN
MaxTime - PeriodTime

This is the report view of the table at this point:

test1 is the MAX(Dates[Date]) respecting the Aug 2020 filter

test2 is the MIN(Dates[Date]) respecting the Aug 2020 filter

test5 is the MIN(Dates[Date]) removing all filters

test3 is the total of all billing amounts for the filtered period

test4 is the measure above not working as I would like it to. 

What I would like to see from test4 is $6688 which is the difference between VAR MaxTime from Test4 ($82,801.50) and the total billing amount through August 2020 (Test4 VAR AllCurrentTime, in theory) including previous months ($76,113.50) that I can't seem to get it to pull correctly. $17,746.00 (Test4 VAR PeriodTime) is the correct billing amount for Aug 2020 only.

What do I need to add to 

Var AllCurrentTime = CALCULATE(SUM('Time Entries'[Billable_Amt]),'Time Entries'[Included In Billable Hours], ALL(Dates), FILTER(Dates, Dates[Week] <= MaxDate))

To get the total billing amount between 2/24/19 and 8/31/20? I feel like all the data is here, I am just missing something that is likely quite obvious.

[lbendlin]

Can you confirm that your slicer is fed by an unconnected table?

[KayceVC]

The slicers causing me issues are the Dates and Months slicers. Both slicers are controlled by their own tables with the Months having a relationship with Dates and Dates having a relationship with the Time Entries.