Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Calculating maximum of rolling sum

Hi,

 

I have a table with the list of article numbers and movement dates - the table is sorted by article number and then, chronologically, by movement dates. Then, in the third column, I have a quanity of article units moved in a given date. In the fourth column, I have a measure called "Rolling" which gives me for each movement date for each article the sum of article units moved in last X days, where X is the average delivery time for each article (in days) + 3 days. The measure is calculated in a following way:

 

Rolling =
VAR CurrentArticleNumber = MAX(table_name[ARTICLE_NUMBER])
VAR CurrentDate = MAX(table_name[MOVEMENT_DATE])
VAR DeliveryTime = AVERAGE(table_name[another_table[DELIVERY_TIME])
VAR StartDate = CurrentDate - DeliveryTime - 3
RETURN
CALCULATE (
    SUM(table_name[QUANTITY]),
    FILTER(
        ALL(table_name),
        table_name[ARTICLE_NUMBER]= CurrentArticleNumber &&
        table_name[MOVEMENT_DATE]>= StartDate &&
        table_name[MOVEMENT_DATE] <= CurrentDate
    )
)

 

The measure works and gives me the values I want to obtain (see the table below).

ARTICLE_NUMBERMOVEMENT_DATEAvg of DELIVERY_TIMESum of QUANTITYRolling
100004.10.2023 00:0010-1-1
100006.10.2023 00:0010-1-2
100011.10.2023 00:0010-1-3
100012.10.2023 00:0010-2-5
100017.10.2023 00:0010-1-6
100019.10.2023 00:0010-1-6
100020.10.2023 00:0010-1-6
100206.10.2023 00:005-30-30
100224.10.2023 00:005-2-2
100206.11.2023 00:005-6-6
100209.11.2023 00:005-1-7
100210.11.2023 00:005-22-29
100214.11.2023 00:005-3-32
100419.10.2023 00:005-9-9
100402.11.2023 00:005-7-7
100510.10.2023 00:005-2-2
100511.10.2023 00:005-2-4
100526.10.2023 00:005-6-6
100528.10.2023 00:005-2-8
100530.10.2023 00:005-4-12

 

What I want to achieve in the next step, is to calculate the minimum (or maximum, if we look at absolute values) of the "Rolling" measure for each article number. Based on the data I provided, I would like to have a following table: 

ARTICLE_NUMBERNew_measure
1000-6
1002-32
1004-9
1005

-12

 

What should "New_measure" look like?

 

Many thanks in advance,

Daniel

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi SamInogic ,

     

    unfortunately, the solution you have suggested doesn't work - I simply get blanks as values in the column. Luckily, with help of ChatGPT, I've managed to create a measure that works:

    New_measure =
    VAR SummaryTable =
        SUMMARIZE (
            'Table',
            'Table'[ARTICLE_NUMBER],
            "Min_Rolling", MINX ( FILTER ( 'Table', 'Table'[ARTICLE_NUMBER] = EARLIER ( 'Table'[ARTICLE_NUMBER] ) ), [Rolling] )
        )
    RETURN
        MINX ( SummaryTable, [Min_Rolling] )
     
    Nevertheless, thank you very much for your help!
     
    BR,
    Daniel

3 Replies

  • SamInogic's avatar
    SamInogic
    Icon for Super User rankSuper User
    2 years ago

    Hi Anonymous ,

    Try below DAX for new Measure :

    New_measure =
    VAR MinRolling = CALCULATE(MIN('Table'[Rolling]), ALLEXCEPT('Table', 'Table'[ARTICLE_NUMBER]))
    RETURN
        IF(ISBLANK(MinRolling), BLANK(), MinRolling)


    This will give you result as : 

     

    Thanks!

    Inogic Professional Service Division

    An expert technical extension for your techno-functional business needs

    Power Platform/Dynamics 365 CRM

    Drop an email at [email protected]

    Service:  http://www.inogic.com/services/ 

    Power Platform/Dynamics 365 CRM Tips and Tricks:  http://www.inogic.com/blog/



    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi SamInogic ,

       

      unfortunately, the solution you have suggested doesn't work - I simply get blanks as values in the column. Luckily, with help of ChatGPT, I've managed to create a measure that works:

      New_measure =
      VAR SummaryTable =
          SUMMARIZE (
              'Table',
              'Table'[ARTICLE_NUMBER],
              "Min_Rolling", MINX ( FILTER ( 'Table', 'Table'[ARTICLE_NUMBER] = EARLIER ( 'Table'[ARTICLE_NUMBER] ) ), [Rolling] )
          )
      RETURN
          MINX ( SummaryTable, [Min_Rolling] )
       
      Nevertheless, thank you very much for your help!
       
      BR,
      Daniel
  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Icon for Super User rankSuper User
    2 years ago

    Hello SamInogic,

     

    Can you please try this:

    Min Rolling Per Article = 
CALCULATE(
    MIN('table_name'[Rolling]),
    ALLEXCEPT('table_name', 'table_name'[ARTICLE_NUMBER])
)