topic Re: Last week in the table calculation in DAX Commands and Tips

Last week in the table calculation

danielcontente — Thu, 05 Jan 2023 10:04:51 GMT

Hi, i have the following table, and would like to create a measure in which i will divide each item in the column "#Total Accumulative Amount" by the value in the column "Max Expected Amount" (26,834,488)

The desired result should be a column with the following numbers:

Thank you so much in advance.

Re: Last week in the table calculation

tamerj1 — Thu, 05 Jan 2023 11:16:52 GMT

Hi danielcontente
Please try

= DIVIDE ( [#Total Accumulative Amount], CALCULATE ( [#Total Accumulative Amount], ALL ( 'Date'[Year Week number] ) ) )

Re: Last week in the table calculation

danielcontente — Thu, 05 Jan 2023 15:12:30 GMT

Thanks so much tamerj1

however, i use a slicer for week, so if i filter for week 52, for example, there would be a new #max expected amount in week 52 and not 53. In this case, how can i do in order to consider the max value in the table depending on the filter?

Re: Last week in the table calculation

tamerj1 — Thu, 05 Jan 2023 15:17:12 GMT

danielcontente

Try

=
DIVIDE (
[#Total Accumulative Amount],
CALCULATE (
[#Total Accumulative Amount],
ALLSELECTED ( 'Date'[Year Week number] )
)
)

Re: Last week in the table calculation

danielcontente — Thu, 05 Jan 2023 15:20:13 GMT

example:

If i filter for week 52, then the table below would show only until week 52, and therefore all the values in the column "#Total Accumulative Amount" should be divided by the #Max Amount of week 52..

Re: Last week in the table calculation

danielcontente — Thu, 05 Jan 2023 15:32:53 GMT

Hi tamerj1

it didnt work.

It keeps dividing by the max amount not the amount of the max week.

Example:

because the amount in week 52 > week 53, even if a select week 53, it will divide by the amount of week 52.

Re: Last week in the table calculation

tamerj1 — Thu, 05 Jan 2023 16:05:54 GMT

danielcontente

Please try

=
DIVIDE (
[#Total Accumulative Amount],
MAXX (
TOPN (
1,
ADDCOLUMNS (
ALLSELECTED ( 'Date'[Year Week number] ),
"@MaxAmount", [#Max Amount]
),
'Date'[Year Week number]
),
[@MaxAmount]
)
)

Re: Last week in the table calculation

danielcontente — Thu, 05 Jan 2023 16:20:01 GMT

working perfectly!!

appreciated it tamerj1