Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Cumulative Total

Hi Team,

 

I am trying to create coulumn contain cummlative total of quantites(MGO1) and there are two conditions which i need to full fill:

 If delivery date <=today(week num) then i just want cummlative total quantites(MG01) upto today and if delivery date >Today then values should remain same .

 

I am able to achieve my first condition (Sales YD) in which if delivery date (weeknum)<= weeknum(today) then my total /sum is coming correct but as soon there is future date is there my formula la is giving total sales/sum of MG01 Column .

Please see below screenshot for better under standing: 

My desire result should come like this :

 

Request you to please help me out as i am not able to understand what to do .

amitchandak 

Ashish_Mathur 

Thanks in advacne !

Ashish 

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

27 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Ahmed,

      You are a star. Thank you so much for this . I got the solution .

      I really appreicate this .

      Regards

      Ashish

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Ahmedx ,

      Hope you are doing well.

      Happy New Year !

      I need futher assistance if you can help ..there is one more requirement now in which :

      1) I need to show cummulative total of "Available QTY" if Avilable Date is or before today's date and if MRP Element="Stock" then only it should give me the total values and if date is of future then it should populate total  Available QTY in Future JWeek .

      2) If MRP Element <> "Stock" then i need cummulative total of "Req Qty " if date is or before total else data should populate in Future week .

      Please see below data set if you can help in creating this as well :

       

      I tired the above formula which you share and added one more filter which is showing below but i am not getting my desire result .

      Request you to please help asap.


       

      Thanks,

      Ashish

      • Ahmedx's avatar
        Ahmedx
        Super User
        2 years ago

        where is the example and the desired result?

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    Super User
    2 years ago

    Try the following code:

    Column 2 =
    VAR beforeToday =
    CALCULATE(
        [Sum_of_MG01],
        FILTER(ALLSELECTED(ZIBP_PO_MGRTN),
        ZIBP_PO_MGRTN[DELIVERY_DATE] <= MAX(ZIBP_PO_MGRTN[DELIVERY_DATE])))

    VAR afterToday =
    CALCULATE(
        [Sum_of_MG01],
        FILTER(ALLSELECTED(ZIBP_PO_MGRTN),
        ZIBP_PO_MGRTN[DELIVERY_DATE] > MAX(ZIBP_PO_MGRTN[DELIVERY_DATE])))


    RETURN
    IF(ZIBP_PO_MGRTN[DELIVERY_DATE] <= TODAY(), beforeToday, afterToday)
    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Bibiano_Geraldo ,

      I tired the solution however i am getting error in If condition as Delivery Date column its not taking when i tried to create measure but when i tried creatin a column its working but values are not coming right .Please see below :
      Measure :

      Column :when i created this formula as a column its working but its not giving me right values of MG01 as its doing sum of whole column :

      Let me know if you can help in this .

       

      Thanks

      Ashish

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    Hi,

    Create a Calendar Table.  Create a relationship (Many to One and Single) from the Date column of the Data Table to the Date column of the Calendar Table.  To your visual, drag the Date column from the Calendar Table.  This pattern should work

    Total = sum(Data[Sales])

    YTD sales = calculate([total],datesytd(calendar[date],"31/12")

    Measure = if(min(calendar[date])>today(),[Total],[YTD sales])

    Hope this helps.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Ashish_Mathur ,

       

      Thank you for your response. I tried the way you suggested and created one calendar table from the date which needs to be taken as a refernce and created 3 measures as per your suggestion Total, YTD ,Measure however as soon i dragged the "Measure" column in the table its not giving me the correct result instead of this no of rows has been multiplied . 

      I need cumulative data untill today's date and if date is of future then data shoudl remain same for that date :

      Please see below :

      Solution which i am looking for is:

  • TomMartens's avatar
    TomMartens
    Super User
    2 years ago

    Hey Anonymous ,

     

    unfortunately, the sample data does not contain the columns you used in your posts. This makes it difficult to follow the previous discussion and trying to provide a solution.

    Next to that, when I'm looking at the calendar table, the calendar week number does not contain the year number. For this reason it can become difficult. From your initial post the condition  "delivery date <=today(week num)" seems to be not correct because you are comparing the delivery date with a weeknumber.

    I'm also not sure what you mean by today, do you really mean Today or are you referring to the current data, when creating a visual referencing the Calendar table?

    Not sure, but are you aware of the articles here: https://www.daxpatterns.com/time-patterns/

     

    Consider explaining how the sample data relates to the available discussion.

     

    Regards,

    Tom

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi TomMartens ,

      Thank you for ypour response. Due to data security i am not able to share original data and thats why i created  sample file with similar kind of columns.

      As you mentioned in my first post I mentioned delivery date <= Today(Weeknum) , i actually mean to say 
      Delivery date<= Today() .

      And in Visual I have tried couple of things in which i tried one calendar table by taking reference to Delivery Date column and create a Many to One relationship between Calendar Table (date) with ZIBP_PO_MATIN(Delivery date(date)) column .

       

      Thanks,

      Ashish

  • o_belov's avatar
    o_belov
    Advocate II
    2 years ago

    Anonymous , try to use this logic

    Sales_YD =

     

    VAR MaxDate = MAX (ZIBP_PO_MGRTN[DELIVERY_DATE])
    VAR MaxYear = YEAR(MaxDate )
    RETURN
     
            CALCULATE(
                [sum_of_MG01],
                FILTER (
                ALL( ZIBP_PO_MGRTN),
                ZIBP_PO_MGRTN[DELIVERY_DATE] <= MaxDate
                && YEAR( ZIBP_PO_MGRTN[DELIVERY_DATE]) = MaxYear
               
                    )
             )
    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi o_belov ,

      Thank you for your response. 

      I tried the solution though its working but i am getting the result of whole column.

      See it's giving me cummulative total of whole column.

       

      Thanks,

      Ashishh