Forum Discussion

2NV_DB's avatar
2NV_DB
Frequent Visitor
2 years ago

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

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



4 Replies

  • Devender_Power's avatar
    Devender_Power
    Regular Visitor
    2 years ago

    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.

     

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    2 years ago

    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's avatar
      2NV_DB
      Frequent Visitor
      2 years ago

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

      The _condition returns 5 as well.