- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

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

[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-21-2019 04:51 AM - edited 01-22-2019 02:49 AM

Hello,

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

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:

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)

Solved! Go to Solution.

Accepted Solutions

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-22-2019 02:48 AM

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.

All Replies

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-21-2019 09:53 PM

Hi @Scitech,

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))

For more details, please check the pbix attached.

Regards,

Frank

If this post

**helps**, then please consider

**to help the other members find it more quickly.**

*Accept it as the solution*## Re: Context, return a value based on max (columnvalue) this month vs previous month

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-22-2019 02:48 AM

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.

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-22-2019 03:30 AM

please check this code.

it might work.