Forum Discussion
How to Get Max to work while grouping data
I have been struggling with a problem a couple times and wanted to know if there was a solution someone has for this problem.
The issue is how do I get MAX to work when grouping data.
| Project Number | Asset Group | Forecast Date | Amount |
| Project 1 | Asset 1 | 23-Jul-18 | 11 |
| Project 1 | Asset 1 | 23-Jun-18 | 29 |
| Project 1 | Asset 1 | 24-May-18 | 12 |
| Project 2 | Asset 1 | 25-Jul-18 | 18 |
| Project 2 | Asset 1 | 25-Jun-18 | 13 |
| Project 2 | Asset 1 | 26-May-18 | 27 |
| Project 3 | Asset 2 | 26-Jul-18 | 20 |
| Project 3 | Asset 2 | 26-Jun-18 | 21 |
| Project 3 | Asset 2 | 27-May-18 | 28 |
Dax Formulas I am using
Max Project Date = CALCULATE(MAX(Forecast Date),
FILTER(ALL(Forecast Date), Forecast Date < MAX (Calendar Filter Date))) Forecast Amount = CALCULATE(SUM(Amount),
FILTER(ALL(Forecast Date),Forecast Date = [Max Project Date]))
If I filter Project 1 for Jul = 11 (the correct outcome)
When I filter Asset 1 for Jul = 18 (Max of Project 2). What I want the outcome to be is 29 (11+18) being the max of each project added together.
Does anyone know how to write this so it works for both a project and asset filter? This needs to work based on a Max function as there can be duplicate forecasts in a month and only the most recent is needed per project and months where there is no forecast and it would grab the most recent prior to the forecast period.
I had a go at a caluclated column but I couldnt get the calendar filter to work as filters do not come accross when doing calculated columns.
Thanks in advance
7 Replies
Hi,
This measure works. You may download my PBI file from here.
=SUMX(FILTER(SUMMARIZE(Data,'Calendar'[Year],'Calendar'[Month],"EFGH",CALCULATE(FORMAT(MAX(Data[Forecast Date]),"mmyy"),ALL('Calendar')),"IJKL",FORMAT(MAX('Calendar'[Date]),"mmyy"),"ABCD",SUM(Data[Amount])),[IJKL]=[EFGH]),[ABCD])
Hope this helps.
Thanks Ashish,
You have introduced me to a new function I now need to learn being SUMMARIZE.
The solution you have proposed works for July but in the pbix you have saved doesnt work when the filter is updated to prior months. In appication, I am trying to show what a forecast was at any given period not just at the most recent. There are also circumstances where multiple forecasts are loaded and I only want the most recent as well as times where no forecast is loaded in the month and I want to gave the most recent prior to the filter period. I have attached some better data to show this and the expected outcomes.
At the moment my calendar is not related to my data table as I only need it for the filter measure not necessarily to filter the data table.
Project Number Asset Group Forecast Date Amount Project 1 Asset 1 23-Jul-18 11 Project 1 Asset 1 23-Jun-18 29 Project 1 Asset 1 24-May-18 12 Project 2 Asset 1 25-Jul-18 18 Project 2 Asset 1 27-May-18 13 Project 2 Asset 1 22-May-18 27 Project 3 Asset 2 26-Jul-18 20 Project 3 Asset 2 26-Jun-18 21 Project 3 Asset 2 27-May-18 28
Asset 1 Output Jul-18 11+18 = 29 Jun-18 29+13 = 42 May-18 12+13 = 25 Hi,
I do not understand your requirement. For May, why should the output be 12+13? For June, why should it be 29+13?
