Forum Discussion
KayceVC
5 years agoHelper II
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...
- 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 ) ) ) ) ) )
KayceVC
5 years agoHelper II
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
)
)
)
)
)
)