Accumulated Sales total by MTD perspective

[awolf88]

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 

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.

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 :

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. 

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. 

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

[v-yueyunzh-msft · Accepted solution]

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:

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

[awolf88]

this worked like a charm, Aniya! 

Thank you sooo so much for this!

Best wishes,

Alex

[lbendlin]

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.