Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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.

 

 

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)

 

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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.

     

     

     

3 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Icon for Community Support rankCommunity Support
    7 years ago

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

     

    For more details, please check the pbix attached.

     

    Regards,

    Frank

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        Anonymous
        Not applicable
        7 years ago

        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