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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
awolf88
Helper II
Helper II

Accumulated Sales total by MTD perspective

Hi guys, 

 

I'm having some issues with a bunch of date-columns and programming Dax code to get the expected outcome. Perhaps you can help. 

 

Here's my situation: 

I have 2 tables, one for historic Sales 

awolf88_0-1668764504000.png

 

and one for Open Orders in my books. Now this data-set has 2 date columns, one with expected shipping date and another that reads the date it was "INSERTED" aka "ARCHIVED" on.

 

awolf88_1-1668764607227.png

 

Now i also have a dim.date table i connect the two with each other, since you can see that i don't necessarily have a date entry in the sales table (not every day reads a sale). Connection is as such :

awolf88_2-1668764728851.png

 

My next step is to summarize the values of "Open Orders" + "Sales" to equal what i refer to as "Projected outcome" by a monthly view. 

awolf88_3-1668764917316.png

Now I've come this far, but would be interested in viewing this "Projected" outcome in a different granularity: I would like to view the Projected total by every Date_Archived I have (or every day of the current running month if you wish). Therefore I would like to see view how the projected outcome of the running + future months (in our case November + December) change by day as displayed. Since we are currently in November today, i would like to backtrack to November 1st until today. 

awolf88_4-1668765235258.png

Now as you can see, everything theoreticall functions as it should with one exception: 

The "Open Orders" value decreases per running month as it should as my datasets "work themselves off". every entry there will transform from an open Order -> Sale. One diminishes while the other grows and If no additional orders come in and the all open orders go out on a specific date, the Projected total doesnt change. 

 

But my"Sales" measure here shows me the value of the according sales per day itself and I would need to to show the cumulative total MTD per Date_Archived while only fetching the totals of the according month displayed in relation to the dim.Date table. 

 

so in the last screenshot, if 01.11. has €100 in revenue and 03.11. has €1.320, i would need it to read €1.420 for that entry as a cumulative total. 

 

Hope that makes sense.

 

Could you kindly help with that measure, my friends? 

I've uploaded the demo data ive initially setup to help explain. 

https://www.dropbox.com/sh/s8mzj9p2el83io2/AABPBqdwKk2WUYkxmL33vMIXa?dl=0

 

Thank you so much for all your help in advance as always. Truly love this community!

Best,

Alex

 

 

 

 

1 ACCEPTED SOLUTION
v-yueyunzh-msft
Community Support
Community Support

Hi , @awolf88 

According to your description, you want to "if 01.11. has €100 in revenue and 03.11. has €1.320, i would need it to read €1.420 for that entry as a cumulative total.". Right?

I have donload your .pbix file and test in my side.Here are the steps you can refer to :

(1)We can create a measure to replace your [Sales] measure:

Sales = var _current_date =MAX('Open Orders'[Date_Archived])
var _t = FILTER('Sales','Sales'[Sales Date]<=_current_date)
 return
SUMX(_t,[Revenue])

(2)Then we put this measure on the visual and we will meet your need:

vyueyunzhmsft_0-1669009273798.png

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

 

View solution in original post

3 REPLIES 3
v-yueyunzh-msft
Community Support
Community Support

Hi , @awolf88 

According to your description, you want to "if 01.11. has €100 in revenue and 03.11. has €1.320, i would need it to read €1.420 for that entry as a cumulative total.". Right?

I have donload your .pbix file and test in my side.Here are the steps you can refer to :

(1)We can create a measure to replace your [Sales] measure:

Sales = var _current_date =MAX('Open Orders'[Date_Archived])
var _t = FILTER('Sales','Sales'[Sales Date]<=_current_date)
 return
SUMX(_t,[Revenue])

(2)Then we put this measure on the visual and we will meet your need:

vyueyunzhmsft_0-1669009273798.png

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

 

this worked like a charm, Aniya! 

Thank you sooo so much for this!

 

Best wishes,

Alex

lbendlin
Super User
Super User

Do you want to show revenue and order value in the same chart?  Usually revenue takes precedence (so that you don't double count).

 

Your sample data does not fully cover the issue.  Please resubmit better sample data.

 

lbendlin_0-1668889312617.png

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors