Forum Discussion

TheGreatYOLO's avatar
TheGreatYOLO
Frequent Visitor
7 years ago

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.    Pro...
Need Help
Ashish_Mathur's avatar
Ashish_Mathur
Super User
7 years ago

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.

 

TheGreatYOLO's avatar
TheGreatYOLO
Frequent Visitor
7 years ago

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 NumberAsset GroupForecast DateAmount
Project 1Asset 123-Jul-1811
Project 1Asset 123-Jun-1829
Project 1Asset 124-May-1812
Project 2Asset 125-Jul-1818
Project 2Asset 127-May-1813
Project 2Asset 122-May-1827
Project 3Asset 226-Jul-1820
Project 3Asset 226-Jun-1821
Project 3Asset 227-May-18

28

 

 

 Asset 1 Output
Jul-1811+18 = 29
Jun-1829+13 = 42
May-1812+13 = 25

 

 

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    Hi,

     

    I do not understand your requirement.  For May, why should the output be 12+13?  For June, why should it be 29+13?

    • TheGreatYOLO's avatar
      TheGreatYOLO
      Frequent Visitor
      7 years ago

      In June the most recent forecast prior to 30-Jun for Project 1 is 29 (date 23-jun), for Project 2 is 13 (date 27-May).

       

      For May Project 1 is 12 (24-May) and project 2 is 13 (27-May).

       

      This is the most recent prior to the filter date. It doesnt matter what period it relates to it is just trying to find what was the most recent forecast prior to the date.

       

      The forecast period in this example is the date the forecast was uploaded not the period the amount relates to.

      • v-chuncz-msft's avatar
        v-chuncz-msft
        Community Support
        7 years ago

        TheGreatYOLO,

         

        You may try the measure below.

        Measure =
SUMX (
    SUMMARIZE ( Table1, Table1[Project Number], Table1[Asset Group] ),
    CALCULATE (
        SUMX ( TOPN ( 1, Table1, Table1[Forecast Date], DESC ), Table1[Amount] )
    )
)