topic Re: Different result when using Measure as a variable vs using a scalar. in DAX Commands and Tips

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

2NV_DB — Thu, 06 Jun 2024 01:21:53 GMT

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

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

Jihwan_Kim — Thu, 06 Jun 2024 03:57:25 GMT

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

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

2NV_DB — Thu, 06 Jun 2024 05:43:26 GMT

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

The _condition returns 5 as well.

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

2NV_DB — Fri, 07 Jun 2024 02:24:51 GMT

Any other suggestions?

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

Devender_Power — Fri, 07 Jun 2024 06:51:01 GMT

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.