Create a dynamic time range for forecasting Acquisition count in a certain mount of days, based on users’ selection.
Solution:
1.Calculate the number of days from Refresh Date to the future Closing Date.
Base Measure:
Acquisition Count Total =
CALCULATE(
DISTINCTCOUNT( Acquisition[Property Acquisition Key] ),
Acquisition[Property Acquisition Wire Sent Date Key] <> 0 )
Calculated Column:
Refresh to closing days =
CALCULATE(
COUNTROWS(‘Date’),
DATESBETWEEN(‘Date'[Date], Acquisition[Refresh Date], Acquisition[Property Acquisition Closing Date] – 1 ),
ALL(‘Date’)
)
2.Create a disconnected table which contains the numbers for users to select.
3.Create a relationship between the two number of days using DAX.
Selected Acquisition Count =
VAR CountWithinFiveDays =
CALCULATE(
‘Measure'[Acquisition Count Total],
Acquisition[Refresh to closing days] >= 1 && Acquisition[Refresh to closing days] <= 5
)
VAR CountWithinTeneDays =
CALCULATE(
‘Measure'[Acquisition Count Total],
Acquisition[Refresh to closing days] >= 1 && Acquisition[Refresh to closing days] <= 10
)
VAR CountWithinTwentyDays =
CALCULATE(
‘Measure'[Acquisition Count Total],
Acquisition[Refresh to closing days] >= 1 && Acquisition[Refresh to closing days] <= 20
)
VAR Result =
IF(
HASONEVALUE(‘Heat Map Number'[No.] ),
SWITCH(
VALUES(‘Heat Map Number'[No.] ),
5, CountWithinFiveDays,
10, CountWithinTeneDays,
20, CountWithinTwentyDays
)
)
RETURN
Result
