Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
ashc_cool10
Helper III
Helper III

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: Capture_11.JPG

My desire result should come like this :

Capture_111.JPG

 

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

@amitchandak 

@Ashish_Mathur 

Thanks in advacne !

Ashish 

1 ACCEPTED SOLUTION
Ahmedx
Super User
Super User

Is this what you are looking for?

Screenshot_8.png

View solution in original post

27 REPLIES 27
Ahmedx
Super User
Super User

Is this what you are looking for?

Screenshot_8.png

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 :

 

CHeck_Data.JPG

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.

CHeck_Data_2.JPG
CHeck_Data_3.JPG

 

Thanks,

Ashish

where is the example and the desired result?

Hi @Ahmedx ,

 

Here is the example.
CHeck_Data.JPG
Note-In yellow i have mentioned the conditons.

 

Thanks,

Ashish

Hi Ahmed,

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

I really appreicate this .

Regards

Ashish

o_belov
Advocate I
Advocate I

@ashc_cool10 , 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
           
                )
         )

Hi @o_belov ,

Thank you for your response. 

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

ashc_cool10_0-1702832586768.png

ashc_cool10_1-1702832634210.png

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

 

Thanks,

Ashishh

 

TomMartens
Super User
Super User

Hey @ashc_cool10 ,

 

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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

ashc_cool10
Helper III
Helper III

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

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

 

 

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

ashc_cool10
Helper III
Helper III

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

 

Regards,

Ashish

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

Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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 :sol_3.JPGAshish_Sol_2.JPGashish_sol.JPG

Solution which i am looking for is:
SALES_yd.JPG

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi,

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

Regards,

Ashish

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.