Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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
Solved! Go to 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
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
this worked like a charm, Aniya!
Thank you sooo so much for this!
Best wishes,
Alex
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
88 | |
85 | |
82 | |
65 | |
49 |
User | Count |
---|---|
138 | |
110 | |
104 | |
66 | |
64 |