topic Re: Refering to Another Measure is giving blank in DAX Commands and Tips

Refering to Another Measure is giving blank

john_elmasry — Fri, 31 Mar 2023 12:23:31 GMT

Hello,

I have only one table with these columns.

Last month column is a flag refers to the latest month in the fact table and it is generated in the sql query itself.

In the dashboard I want to calculate the values of the Previous month to last month which is for example 202304 .. and I don't want to add any time slicer, I want all to happen through measures, I also don't need a date column.

Any way I created a measure to get the Previous Month ID which is 202304 .. the measure is as follow

Previous Month Measure = CALCULATE(SELECTEDVALUE(Fact_Sales[Month_ID]),filter(Fact_Sales, Fact_Sales[Last Month]=1))-1

Then I created another measure to calculate the sales volume based on this Previous Month ID, As follows:

Volume (MT) LY =

CALCULATE(SUM(Fact_Sales[SALES]), FILTER(Fact_Sales, Fact_Sales[Month_ID] = [Previous Month Measure]))

The result is BLANK()

However, when I add the Previous Month Definition inside the second measure as a parameter, it works perfectly.

Volume (MT) LY = VAR PREVIOUS_MONTH_VARIABLE = CALCULATE(min(Fact_Sales[Month_ID])-1,filter(Fact_Sales, Fact_Sales[Last Month]=1))

Return

CALCULATE(SUM(Fact_Sales[SALES]), FILTER(Fact_Sales, Fact_Sales[Month_ID] = PREVIOUS_MONTH_VARIABLE))

I need to understand why the first method is not working, also if I wanted to use the first method as splitting it to 2 measures, what should I do?

Kindly find a demo in the url.

https://drive.google.com/file/d/1M3y_cmFKgP2gFAiT-IQUt09n9Mt9KiEJ/view?usp=sharing

Thanks

Re: Refering to Another Measure is giving blank

john_elmasry — Fri, 31 Mar 2023 12:54:46 GMT

I found a solution for that but don't know if this is the optimum one or no, change the measure to

Previous Month Measure = CALCULATE(SELECTEDVALUE(Fact_Sales[Month_ID]),filter(ALL(Fact_Sales), Fact_Sales[Last Month]=1))-1

Re: Refering to Another Measure is giving blank

v-zhangti — Mon, 03 Apr 2023 05:18:45 GMT

Hi, john_elmasry

Yes. Your original formula is pretty close to the correct answer, but you need to add an ALL filter at the critical time.

ALL function (DAX) - DAX | Microsoft Learn

Or try this:

Previous Month Measure = CALCULATE(MAX(Fact_Sales[Month_ID]),filter(ALL(Fact_Sales), Fact_Sales[Last Month]=1))-1

Best Regards,

Community Support Team _Charlotte

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