Measure branching Vs variables in a larger measure

[timmay]

Hi guys,

Why do we need to separate some larger measures into smaller measures in order to get correct results?

For example, one larger measure can be:

LargeMeasure =

Variable A = 1
Variable B = A + 2
Variable C = B + 3

RETURN C

This over simplified example measure returns incorrect results but If I split it into smaller measures, the results are correct, for example:

SmallMeasure1 =
Variable A = 1
RETURN A

SmallMeasure2 =
Variable B = SmallMeasure1 + 2
RETURN B

SmallMeasure3 =
Variable C = SmallMeasure2 + 3
RETURN C

If I then use small measure3 in a table, it returns correct results, but LargeMeasure’s results would be incorrect. Why is that?

An actual example that I have is the following code:

Daily Qty Forecast =

VAR tbl =

// To calculate the daily forecast qty by taking the total amount and dividing by the days in the month



  



ADDCOLUMNS(



    SUMMARIZE(



        'Sales Forecast Inactive Date Relationship',



        'Sales Forecast Inactive Date Relationship'[Date],



        'Sales Forecast Inactive Date Relationship'[Month End Date]



    )



    ,



    "@ForecastQty",



    [Sales Forecast Qty Inactive Date Relationship] ,



    "@Days",



    DATEDIFF(



        'Sales Forecast Inactive Date Relationship'[Date],



        'Sales Forecast Inactive Date Relationship'[Month End Date],



        Day



    ),



    "@DailyQty",



    DIVIDE(



        [Sales Forecast Qty Inactive Date Relationship],



        DATEDIFF(



            'Sales Forecast Inactive Date Relationship'[Date],



            'Sales Forecast Inactive Date Relationship'[Month End Date],



            Day



        ) + 1



    )



)

VAR SumQty =

SUMX(



    tbl,



    [@DailyQty]



)

VAR FilterDates =

// Returns the daily amount for each day in the date table relating to the relevant month



CALCULATE(



    SumQty,



    FILTER(



        VALUES( 'Sales Forecast Inactive Date Relationship'[Date] ),



        'Sales Forecast Inactive Date Relationship'[Date] <= MAX( Dates[Date] )



    ),



    FILTER(



        VALUES( 'Sales Forecast Inactive Date Relationship'[Month End Date] ),



        'Sales Forecast Inactive Date Relationship'[Month End Date] >= MIN( Dates[Date] )



    )



)

VAR AddDates =

// Put the amounts into a table with every day

ADDCOLUMNS(



    VALUES(Dates[Date]),



    "Amount",



   FilterDates



)

VAR SumFinal =

// SumX on the table to return a scalar value



SUMX(



    AddDates,



    [Amount]



)

RETURN

// Final result



SumFinal

[timmay]

Thank you for the reply.

However, I tried a solution based on this and couldn't get the results I'm after. 

Still spliting into separate measures gives me correct answers but compiling into 1 measure doesn't, even with using calculate on previous variables.  

We have measure 1:

1 Forecast Table Daily Working = 

VAR tbl = 

    // To calculate the daily forecast qty by taking the total amount and dividing by the days in the month
        
    ADDCOLUMNS(
        SUMMARIZE(
            'Sales Forecast Inactive Date Relationship',
            'Sales Forecast Inactive Date Relationship'[Date],
            'Sales Forecast Inactive Date Relationship'[Month End Date]
        )
        ,
        "@ForecastQty",
        [Sales Forecast Qty Inactive Date Relationship] ,
        "@Days",
        DATEDIFF(
            'Sales Forecast Inactive Date Relationship'[Date],
            'Sales Forecast Inactive Date Relationship'[Month End Date],
            Day
        ),
        "@DailyQty",
        DIVIDE(
            [Sales Forecast Qty Inactive Date Relationship],
            DATEDIFF(
                'Sales Forecast Inactive Date Relationship'[Date],
                'Sales Forecast Inactive Date Relationship'[Month End Date],
                Day
            ) + 1
        )
    )

VAR SumQty =

        
    SUMX(
        tbl,
        [@DailyQty]
    )


RETURN
 
    // Final result
 
    SumQty 

and measure 2

2 Forecast Table Filter = 

VAR FilterDates =
    // Returns the daily amount for each day in the date table relating to the relevant month
    CALCULATE(
        [1 Forecast Table Daily Working],
        FILTER(
            VALUES( 'Sales Forecast Inactive Date Relationship'[Date] ),
            'Sales Forecast Inactive Date Relationship'[Date] <= MAX( Dates[Date] )
        ),
        FILTER(
            VALUES( 'Sales Forecast Inactive Date Relationship'[Month End Date] ),
            'Sales Forecast Inactive Date Relationship'[Month End Date] >= MIN( Dates[Date] )
        )
    )


RETURN
 
    // Final result
 
    FilterDates
    

 gives different results than the 2 measures together in 1, with an added calculate

Daily Qty Forecast = 

VAR tbl = 

    // To calculate the daily forecast qty by taking the total amount and dividing by the days in the month
        
    ADDCOLUMNS(
        SUMMARIZE(
            'Sales Forecast Inactive Date Relationship',
            'Sales Forecast Inactive Date Relationship'[Date],
            'Sales Forecast Inactive Date Relationship'[Month End Date]
        )
        ,
        "@ForecastQty",
        [Sales Forecast Qty Inactive Date Relationship] ,
        "@Days",
        DATEDIFF(
            'Sales Forecast Inactive Date Relationship'[Date],
            'Sales Forecast Inactive Date Relationship'[Month End Date],
            Day
        ),
        "@DailyQty",
        DIVIDE(
            [Sales Forecast Qty Inactive Date Relationship],
            DATEDIFF(
                'Sales Forecast Inactive Date Relationship'[Date],
                'Sales Forecast Inactive Date Relationship'[Month End Date],
                Day
            ) + 1
        )
    )

VAR SumQty =

    SUMX(
        tbl,
        [@DailyQty]
    )

VAR CalcSumQty = 
    
    CALCULATE(SumQty)


//2nd measure starts here

VAR FilterDates =
    // Returns the daily amount for each day in the date table relating to the relevant month
    CALCULATE(
        CalcSumQty,
        //[1 Forecast Table Daily Working],
        FILTER(
            VALUES( 'Sales Forecast Inactive Date Relationship'[Date] ),
            'Sales Forecast Inactive Date Relationship'[Date] <= MAX( Dates[Date] )
        ),
        FILTER(
            VALUES( 'Sales Forecast Inactive Date Relationship'[Month End Date] ),
            'Sales Forecast Inactive Date Relationship'[Month End Date] >= MIN( Dates[Date] )
        )
    )

RETURN
    
    // Final result
     FilterDates 

Results are as shown:

"daily qty forecast" should be the same as "2 forecast table filter" but is not.

Appreciate your time.

Thanks,

Tim

[FreemanZ]

hi @timmay 

Because 1) measures are always hiddenly wrapped with a CALCULATE and 2) sometimes a CALCULATE is necessary to perform context transition, converting a row context to a filter context. 

So if you plan to merge the two:

Measure1 = SUM(tbl[value])

Measure2 = ADDCOLUMNS(VALUES(), "Value", [Measure1])

It is like this:

Measure2 =

ADDCOLUMNS(

    VALUES(),

    "Value",

    CALCULATE(SUM(tbl[value]))

)

p.s. you can PREVIEW the content before you post it.