Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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.
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
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
please check this code.
it might work.
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |