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
Anonymous
Not applicable

Context, return a value based on max (columnvalue) this month vs previous month

Hello,

 

This is my entire dataset: It shows [Project], [Department], [Month], [revision] and [Budget], where [Revision] is the revision-number for the budget.

 

Capture.PNG

 

Problem: I want to show the difference betweem the maximum budget current month, and maximum budget for previous month. Note that previous month, might not have a value (see data-table), so to put it another way: I need to show the difference between the different revisions.

 

My attempt to achive this revolves around these following measures, where;

BudgetMaxLast = Should show the [Budget] for the last (max) revisionnumber. This seems to be working fine.

BudgetPrevious = Should show the [Budget] for the last (max) revisionnumber, from the periode before current month. This is not working correctly

BudgetChange = Shows difference between the two.

 

BudgetMaxLast = 
CALCULATE(max(Sheet1[Budget]);FILTER(ALLEXCEPT(Sheet1;Sheet1[Project]);Sheet1[revision] = max(Sheet1[revision])))

BudgetPrevious = 
CALCULATE(max(Sheet1[Budget]);FILTER(ALLEXCEPT(Sheet1;Sheet1[Project]);Sheet1[revision] < max(Sheet1[revision]));FILTER(ALLEXCEPT(Sheet1;Sheet1[Project]);Sheet1[Month] < max(Sheet1[Month])))

BudgetChange = sumx(SUMMARIZE(Sheet1;Sheet1[Project]);[BudgetMaxLast]-[BudgetPrevious])

 

As it stands, BudgetPrevious seems to be giving me the most trouble. Under some conditions, it work, while under others, it doesn't. See chart below:

Capture2.PNG

 

As this shows, project 6543 has the correct budget for current month (201812), but the BudgetPrevios is wrong. The measure returns 30' instead of 20', and I understand that this is because I use max([Budget]) in this measure, but the logic I want to apply is that there should only be 1 row that this applies to (max [revision]), and so it shouldn't matter whether I use Max or Min. The same is true for project 5432 in 201807 (previous = 15', should have been 7').

 

So there you have it. I've been messing with this for a while now and it's causing some headache (even though I've learned more about context on the way), but still not there yet.

 

Below is a link to the file I've been working on where all the data/measures are. If anyone can help me solve this I'd very gratefull.

Remember, I don't only want to show the difference in budget between revisions, but also between months (Max current month vs Max previous month)

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Thank you for the reply, but this doesn't seem to work exactly right either. First off, if you use Max() - 1, then it will return 0 if Max()=1 (which would give me the wrong result).

I also need the measure to return the budget value from the max(revision) from the previous periode, which this does not.

 

However, using your logic (variables, which I'm not too familiar with) I was able to create a measure that does exactly what I want 🙂

 

BudgetPrevious = 
var MaxRev = CALCULATE(max(Sheet1[Revision]);FILTER(ALLEXCEPT(Sheet1;Sheet1[Project]);Sheet1[Revision] < max(Sheet1[Revision]));FILTER(ALLEXCEPT(Sheet1;Sheet1[Project]);Sheet1[Month] < max(Sheet1[Month])))
return
CALCULATE(max(Sheet1[Budget]);Sheet1[Revision] = MaxRev;FILTER(ALLEXCEPT(Sheet1;Sheet1[Project]);Sheet1[Month] < max(Sheet1[Month])))

Someone more knowledgeable than me can probably write this in a more efficient manner, but it atleast return the correct values.

 

 

 

View solution in original post

3 REPLIES 3
v-frfei-msft
Community Support
Community Support

Hi @Anonymous,

 

To create a measure as below.

 

Previousnew = var maxre = CALCULATE(MAX(Sheet1[revision]),ALL(Sheet1),VALUES(Sheet1[Project]))
return
CALCULATE(MAX(Sheet1[Budget]),FILTER(Sheet1,Sheet1[revision]=maxre-1))

Capture.PNG

 

For more details, please check the pbix attached.

 

Regards,

Frank

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Anonymous
Not applicable

Thank you for the reply, but this doesn't seem to work exactly right either. First off, if you use Max() - 1, then it will return 0 if Max()=1 (which would give me the wrong result).

I also need the measure to return the budget value from the max(revision) from the previous periode, which this does not.

 

However, using your logic (variables, which I'm not too familiar with) I was able to create a measure that does exactly what I want 🙂

 

BudgetPrevious = 
var MaxRev = CALCULATE(max(Sheet1[Revision]);FILTER(ALLEXCEPT(Sheet1;Sheet1[Project]);Sheet1[Revision] < max(Sheet1[Revision]));FILTER(ALLEXCEPT(Sheet1;Sheet1[Project]);Sheet1[Month] < max(Sheet1[Month])))
return
CALCULATE(max(Sheet1[Budget]);Sheet1[Revision] = MaxRev;FILTER(ALLEXCEPT(Sheet1;Sheet1[Project]);Sheet1[Month] < max(Sheet1[Month])))

Someone more knowledgeable than me can probably write this in a more efficient manner, but it atleast return the correct values.

 

 

 

Anonymous
Not applicable

@Anonymous

 

please check this code.

it might work.

 

Previousnew = var maxre = CALCULATE(MAX(Sheet1[revision]),ALL(Sheet1),VALUES(Sheet1[Project]))
return
CALCULATE(MAX(Sheet1[Budget]),FILTER(Sheet1,Sheet1[revision]=if((maxre-1)=0,maxre,maxre-1)))
 
Regards,
Ashok T

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.