topic Re: Calculating maximum of rolling sum in DAX Commands and Tips

Calculating maximum of rolling sum

Anonymous — Wed, 10 Apr 2024 11:25:45 GMT

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_NUMBER	MOVEMENT_DATE	Avg of DELIVERY_TIME	Sum of QUANTITY	Rolling
1000	04.10.2023 00:00	10	-1	-1
1000	06.10.2023 00:00	10	-1	-2
1000	11.10.2023 00:00	10	-1	-3
1000	12.10.2023 00:00	10	-2	-5
1000	17.10.2023 00:00	10	-1	-6
1000	19.10.2023 00:00	10	-1	-6
1000	20.10.2023 00:00	10	-1	-6
1002	06.10.2023 00:00	5	-30	-30
1002	24.10.2023 00:00	5	-2	-2
1002	06.11.2023 00:00	5	-6	-6
1002	09.11.2023 00:00	5	-1	-7
1002	10.11.2023 00:00	5	-22	-29
1002	14.11.2023 00:00	5	-3	-32
1004	19.10.2023 00:00	5	-9	-9
1004	02.11.2023 00:00	5	-7	-7
1005	10.10.2023 00:00	5	-2	-2
1005	11.10.2023 00:00	5	-2	-4
1005	26.10.2023 00:00	5	-6	-6
1005	28.10.2023 00:00	5	-2	-8
1005	30.10.2023 00:00	5	-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_NUMBER	New_measure
1000	-6
1002	-32
1004	-9
1005	-12

What should "New_measure" look like?

Many thanks in advance,

Daniel

Re: Calculating maximum of rolling sum

SamInogic — Thu, 11 Apr 2024 04:57:15 GMT

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 crm@inogic.com

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

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

Re: Calculating maximum of rolling sum

Anonymous — Fri, 12 Apr 2024 11:50:20 GMT

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

Re: Calculating maximum of rolling sum

Sahir_Maharaj — Fri, 12 Apr 2024 20:15:26 GMT

Hello SamInogic,

Can you please try this:

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