cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Scitech Regular Visitor
Regular Visitor

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

Accepted Solutions
Scitech Regular Visitor
Regular Visitor

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

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 Smiley Happy

 

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.

 

 

 

3 REPLIES 3
Community Support Team
Community Support Team

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

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

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 other members find it more quickly.
Scitech Regular Visitor
Regular Visitor

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

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 Smiley Happy

 

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.

 

 

 

Ashokt0025 Frequent Visitor
Frequent Visitor

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

@Scitech

 

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