Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
john_elmasry
Frequent Visitor

Refering to Another Measure is giving blank

Hello,

I have only one table with these columns.

john_elmasry_0-1680264839176.png

 

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.
 
Thanks

 

2 REPLIES 2
john_elmasry
Frequent Visitor

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

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors