Forum Discussion

Palmtop's avatar
Palmtop
Helper I
3 years ago
Solved

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:

 

Sample File Link 

 

Lets say I get _ratio = 1.57
 
I would expect to get "Projected Sales" in the first figure as my final result.  Instead I get the below figure, where I only have a single value.  The curve in the first figure was made by replacing "SUM(Sales_Low[sales]) * _ratio" with "SUM(Sales_Low[sales]) * 1.57" meaning _ratio isnt actually a constant.   I assume it has to do with the "IF Sales_Low[date] >= _date" condition.  
 
 
 

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?  
 
I'm still very new to DAX so any additional advice and suggestions are very welcome.  
 
Thank you.  

6 Replies

  • daXtreme's avatar
    daXtreme
    Solution 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.

    • Palmtop's avatar
      Palmtop
      Helper I

      Hello daXtreme,

       

      Thanks for the advice.  I've edited my post with the formatted DAX measure and a link to a sample file.  Let me know if anyone has difficulties accessing it.  Otherwise, I hope this is a good starting spot to help solve the issue.