Forum Discussion

Sam-Ke's avatar
Sam-Ke
New Member
10 months ago
Solved

Calculate Max volumes at Week level

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])
         )
    )

 

 

  • Ahmed-Elfeel's avatar
    Ahmed-Elfeel
    10 months ago

    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.

4 Replies

  • Ahmed-Elfeel's avatar
    Ahmed-Elfeel
    Icon for Super User rankSuper User
    10 months ago

    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's avatar
      Sam-Ke
      New Member
      10 months ago

      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's avatar
        Ahmed-Elfeel
        Icon for Super User rankSuper User
        10 months ago

        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! 😊

  • Praful_Potphode's avatar
    Praful_Potphode
    Icon for Super User rankSuper User
    10 months ago

    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