Calculate Max volumes at Week level

[Sam-Ke]

Hello Everyone,

I am sure similar question was already answered, but i might not have the correct way to formulate my research to find it 😅

I want to calculate the maximum amount of volumes at week level between 2 measures. I want to use it to set the max value of axis for a graph at week level.

To simplify, i have 2 facts tables : stock entries (past deliveries) & the replenishment plan (future deliveries).

In both table, the volumes are given by delivery date. I have calendar linked to both tables and in which i have the week number.

The way i wrote my dax formula, the value returned is the maximum at the Date level.

I want to caculate the maximum from the sum of volumes per week. In my example i expect the 74T from w39 (coming from several deliveries over several days of the week)

I don't know if i can calculate it easily by changing a parameter of my max formula (just changing [date] by [weeknum] doesn't work unfortunately)... or if i should calculate separatly each measure "sum by week".

I tried to look for that last option, but all in examples the week number was in the fact table, and i don't manage to adapt the solution when the week number is in a calendar.

What can you recommend ?

Thanks in advance for taking the time

current measure: 

Max Replenishment / SP =

 MAXX(

    VALUES('Calendar'[Date]),

         CALCULATE(MAX([Factory Past Deliveries], [Replenishment Plan Factory]),

             ALLEXCEPT('Calendar', 'Calendar'[Date])

         )

    )

[Praful_Potphode]

Hi @Sam-Ke ,

if you want max of the two measures in a visual at week level shown in the image, you can simple create a visual Calculation as shown below.

Sample PBIX

Please give kudos or mark it as solution once confirmed.

Thanks and regards,

praful

[Ahmed-Elfeel · Accepted solution]

Hi @Sam-Ke,

Try one of these solutions :

First One : Create Weekly Aggregated Measures First

Factory Past Deliveries Weekly = 
CALCULATE(
    [Factory Past Deliveries],
    ALLEXCEPT('Calendar', 'Calendar'[WeekNum])
)

Replenishment Plan Factory Weekly = 
CALCULATE(
    [Replenishment Plan Factory],
    ALLEXCEPT('Calendar', 'Calendar'[WeekNum])
)

Max Replenishment / SP =
MAXX(
    VALUES('Calendar'[WeekNum]),
    MAX([Factory Past Deliveries Weekly], [Replenishment Plan Factory Weekly])
)

Second Approach: Direct with SUMMARIZE

Max Replenishment / SP =
MAXX(
    SUMMARIZE(
        'Calendar',
        'Calendar'[WeekNum],
        "WeeklyMax",
        MAX(
            CALCULATE([Factory Past Deliveries], ALLEXCEPT('Calendar', 'Calendar'[WeekNum])),
            CALCULATE([Replenishment Plan Factory], ALLEXCEPT('Calendar', 'Calendar'[WeekNum]))
        )
    ),
    [WeeklyMax]
)

Third Approach: with ADDCOLUMNS

Max Replenishment / SP =
MAXX(
    ADDCOLUMNS(
        VALUES('Calendar'[WeekNum]),
        "WeeklyValue",
        VAR PastDeliveries = CALCULATE([Factory Past Deliveries], ALLEXCEPT('Calendar', 'Calendar'[WeekNum]))
        VAR Replenishment = CALCULATE([Replenishment Plan Factory], ALLEXCEPT('Calendar', 'Calendar'[WeekNum]))
        RETURN
            MAX(PastDeliveries, Replenishment)
    ),
    [WeeklyValue]
)

Bonus Tip : 

If you are using this for setting the Y axis maximum in a visual you might want to add a buffer:

Max Replenishment / SP for Axis =
[Max Replenishment / SP] * 1.1  // Adds 10% buffer for better visualization

if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.

[Sam-Ke]

Hello Ahmed,

thanks for taking so much time and offer so many options!

i tried the 3 options. They all returned me indeed the same max amount on a week number. However it isn't sensitive anymor to the Year filter i have : whatever the year selected it sums all available volumes (illustration below - sorry different Names, but same data).

I have the column Week-Year available, so i tried to use it : it returned be the correct value with your option 2 & 3 and worked fine 🙂

thanks for the bonus Tip!

With [WeekNum]

[Ahmed-Elfeel]

Hi @Sam-Ke,

Great to hear the solution worked! If this resolved your problem, could you mark it as a solution?

That way it can help others with similar questions.

If you need any more help with this or have another question, just let me know happy to assist further! 😊