Hi,
We have an Order table which contains Order Date and Delivery Dates. An order taken in one month may not be delivered until many months later.
My users would like to see a column chart which visualizes the number of orders, by Order Date for a given delivery month, but which additionally includes some custom columns which will capture and group all orders for any previous order date months into custom columns, such as Prior Month (PM), Prior Month 2 (PM2), Prior Month 3 and before (PM3+) and also Future, to capture orders with an incorrect order date ahead of its delivery date.
Mock up example:
The reason for this request is that without this, we could have in excess of 200 dates plotted on a chart, whereas the users really want to focus on how we are performing with orders in the current month, whilst retaining the visibility of volume from previous months.
I have had a crack at putting together an initial mock up to see if I could achieve this, but it is far from perfect - please see attached.
Orders for Delivery Month v1.pbix (415.9 KB)
To be able to create the custom buckets on the X Axis, I need actual values in my data. The way I have done this is to create a table called Custom Chart Setup, which is essentially a copy of a basic date table and then added 4 rows to each month.
I have then created a measure [Orders for Custom Column Chart] which checks to see what the selected value of the Custom Column is. If it is one of the Prior Months, then it calculates the number of orders in for that relevant time period. If it is not one of the 4 custom values, it calculates the number of orders for that date value.
Orders for Custom Column Chart =
VAR __selVal = SELECTEDVALUE('Custom Chart Setup'[Custom Column])
Var __SelDate = SELECTEDVALUE('Custom Chart Setup'[True Order Date])
VAR __StartDate = MIN(Dates[Date])
VAR __EndDate = MAX(Dates[Date])
RETURN
IF(__selVal = {"Prior Month"}, CALCULATE([Orders by Delivery Date], Orders[Order Date] >= date(2020,5,1) && Orders[Order Date] <= date(2020,5,31)),
IF(__selVal = {"Prior Month 2"}, CALCULATE([Orders by Delivery Date], Orders[Order Date] >= date(2020,4,1) && Orders[Order Date] <= date(2020,4,30)),
IF(__selVal = {"Prior Month 3 +"}, CALCULATE([Orders by Delivery Date], Orders[Order Date] < date(2020,4,1)),
IF(__selVal = "Future", CALCULATE([Orders by Delivery Date], Orders[Order Date] > __EndDate),
CALCULATE([Orders by Delivery Date], Orders[Order Date] = __selDate))
)
)
)
Result in Power BI
Whilst this works for the chart, it does not allow the user to successfully cross filter from the chart to the Order Detail table. I would like them to be able to click on the Prior Month column and for the Order Detail table to filter to these orders only. This is because there is no relationship between my Orders table and my Custom Chart Setup and I canāt see how one could be created, as the āPrior Monthā assignments are dynamic.
So questions are:
1, Is there a better way of doing this? Or should it be abandoned now and never re-visited 
2, Is there a way to relate it to my Orders table so cross filtering would work.
3, In my measure, for Prior Month 3, 2, 1, I have temporarily hard coded the dates to May 2020, April 2020 and Pre April 2020. This was because I was struggling to do time calculations based on my MIN Dates[Date] value, as it needs to be a column of values to do DATEADD etc. Is there a way to do this in DAX for a fixed date?
Thanks for taking the time to read and any feedback greatly appreciated.
Mark
