Cumulative Total

[Anonymous]

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 

[Ahmedx · Accepted solution]

Is this what you are looking for?

[Anonymous]

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]

where is the example and the desired result?

[Anonymous]

Hi @Ahmedx ,

Here is the example.

Note-In yellow i have mentioned the conditons.

Thanks,

Ashish

[Anonymous]

Hi Ahmed,

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

I really appreicate this .

Regards

Ashish

[o_belov]

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

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

[TomMartens]

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]

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

[Anonymous]

Hi @Bibiano_Geraldo @Ashish_Mathur ,

As requested please see the sample file and let me know if you can able to access this .

https://drive.google.com/file/d/1Ij2Mcp1JsrvKWOIULaEiJzWNQZg5V7tU/view?usp=sharin 

I hope this helps.

Thanks,

Ashish

[Bibiano_Geraldo]

Look for the corrected file bellow, if its what you want, i will explain what's happening

https://drive.google.com/file/d/1k0Vs1nJxMyPG1jU8bckqmrvz5rMcbEBj/view?usp=drive_link

[Anonymous]

Hi @Bibiano_Geraldo ,

Thank you ever so much for your help and i got the solution for this  by @Ahmedx .

Really appreicate your time and efforts.

Regards,

Ashish

[Anonymous]

Hi @Bibiano_Geraldo  @Ashish_Mathur 
Can you please check is there any solution for this ?

Regards,

Ashish

[Bibiano_Geraldo]

It's possible, but I'll need the sample file to make a deep analyse

[Ashish_Mathur]

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]

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:

[Ashish_Mathur]

Hi,

I can help further only when i get access to the PBI file.

[Anonymous]

Hi,

i am not sure how to share the pbix here as i dont see any option 😞

Regards,

Ashish

[Ashish_Mathur]

Upload the file on Google Drive and share the download link.

[Anonymous]

Hi @Ashish_Mathur 

Please see below access of power bi file and let me know if you can able to access it .

https://drive.google.com/file/d/1aIC-S_0Lb3ywiQfE-iyEdDXhItk1Q9JN/view?usp=sharing 

Thanks,

Ashish