Different result when using Measure as a variable vs using a scalar.

[2NV_DB]

Hi there,

I have a dax measure that finds the last date within the exisiting year of a data set and then it extracts the month:

ThisMonthBasedOnInvoices = 
VAR _thisyear = YEAR(TODAY())

VAR _lastinvoicedate = LASTDATE(Consolidated_Sales_ActualTable[Sales_Invoice_date])

VAR _calculation = MONTH(CALCULATE(_lastinvoicedate,FILTER('Financial Calendar','Financial Calendar'[Year]=_thisyear)))

RETURN
_calculation

If I put this measure in a card, I get 5 (which is what I expected).

When I take this measure and use it in a filter of another measure like so:

IncrementalSales24 = 

CALCULATE(([SalesSum24]-[SalesSum23]),
FILTER('Financial Calendar','Financial Calendar'[Month Number]<=[ThisMonthBasedOnInvoices]

))

I get the following chart which is not what I want.

If instead I use a scalar value of 5 like so, I get the chart I do want:

IncrementalSales24 = 

CALCULATE(([SalesSum24]-[SalesSum23]),
FILTER('Financial Calendar','Financial Calendar'[Month Number]<=5

))

I would have expected both charts to be the same. Why is there a difference if the first measure equates to 5?

Essentially I am trying to hide months in my chart that do not yet have data.

Thank you

[Devender_Power]

The issue is that Lastdate is dynamic and because of it  it is not giving correct output try the below

ThisMonthBasedOnInvoices = 
VAR _thisyear = YEAR(TODAY())

VAR _lastinvoicedate = Calculate(LASTDATE(Consolidated_Sales_ActualTable[Sales_Invoice_date]),all(Consolidated_Sales_ActualTable))

VAR _calculation = MONTH(_lastinvoicedate)

RETURN
_calculation

Best Regards

Devender Kumar

If this helps, then please consider Accept it as the solution to help the other members find it more quickly.

[2NV_DB]

Any other suggestions?

[Jihwan_Kim]

Hi,

In my opinion, it is because of context transition when using [measure] inside CALCULATE DAX function.

Row context -> Filter context

CALCULATE function (DAX) - DAX | Microsoft Learn

Please try something like below whether it suits your requirement.

IncrementalSales24 =
VAR _condition = [ThisMonthBasedOnInvoices]
VAR _result =
    CALCULATE (
        ( [SalesSum24] - [SalesSum23] ),
        FILTER (
            'Financial Calendar',
            'Financial Calendar'[Month Number] <= _condition
        )
    )
RETURN
    _result

[2NV_DB]

Thanks for the reply but unfortunately that doesn't work either.
The chart looks like this:

The _condition returns 5 as well.