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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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