Forum Discussion

KayceVC's avatar
KayceVC
Helper II
5 years ago
Solved

Need Measure to Partially Ignore Slicer Filter

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 D...
  • KayceVC's avatar
    5 years ago

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