Forum Discussion
How to "flatten" a variable into constant?
Hello All,
I have 2 curves that contains a low and high case sales scenario. I would like to find the ratio between these 2 curves (Sales High / Sales Low) on the date of the latest monthly actual sales. I then want to use this ratio as a constant to multiply Sales Low over a range of dates.
Test_Measure =
VAR _eval_date =
CALCULATE ( MAXX ( ALLSELECTED ( Actual_Sales ), [date] ) ) // Finds latest sales date
VAR _date =
// If latest sales date is older than 2 months, it will default back to current month.
CALCULATE (
IF (
_eval_date
< DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, 1 ),
DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 ),
_eval_date
)
)
VAR _sales_high =
CALCULATE (
// Value of sales high curve @ _date
MAX ( Sales_High[sales] ),
FILTER ( Sales_High, Sales_High[date] = _date )
)
VAR _sales_low =
CALCULATE (
// Value of sales low curve @ _date
MAX ( Sales_Low[sales] ),
FILTER ( Sales_Low, Sales_Low[date] = _date )
)
VAR _ratio =
DIVIDE ( _sales_low, _sales_high ) // Ratio of Sales Low/Sales High
RETURN
IF (
MIN ( Sales_Low[date] ) >= _date,
CALCULATE ( SUM ( Sales_Low[sales] ) * _ratio )
)
// Multiply the Sales Low curve by _ratio for all future dates
I've included a sample file below:
Is there any way for me to 'flatten' the variable into a constant (1.57) that is invariable for all dates a.k.a. hardcode it?
6 Replies
- daXtremeSolution Sage
First, I'd suggest you follow the rules for DAX formatting: Rules for DAX Code Formatting - SQLBI. DAX that's not formatted the right way is not DAX (and many specialist don't even look at such code). Second, I'd suggest you paste something that can be tinkered with and copied. Nobody wants to type text in from a picture. Third, I'd also kindly suggest that you create a sample file which demonstrates the issue and paste a link to it here so that it's easier for us to troubleshoot. The file should be stored on a shared drive and have public access rights.