Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I am facing an interesting problem which I have been fighting the last days without result unfortunately. Hopefully this will help someone else out in the future as well! The basic idea is to be able to follow up the sales forecast achievement in comparison to normal order inflow pattern.
The below chart shows the average development of monthly order intake. The current month starts at 0 on the X-axis. Ideally all of the monthly orders should have been placed by day 30. Note that 60% of monthly orders are actually placed before the month starts (i.e. 60% of orders for delivery in November are created before 1st Nov).
The dates are rounded into ranges of 5.
I have two problems:
A) Today is the 2nd November, so we are currently on the 0 of X-axis. However, the chart does not show future dates blank but a straight line. How can I get the OrdersVsForecast measure for the next dates ranges BLANK?
A) I would like to see the standard deviation of the measure [AverageDevelopment] on each specific day range. The question to answer is: What is the standard deviation of the ratio "Orders Created by specific date range / Total dataset order qty" based on monthly order inflow.
I have three tables: Forecast, Sales and Ranges connecting as follows:
Sales[DeliveryMonthStart] -- Forecast[MonthStart]
Sales[Range_RoundTo5] -- Ranges[DaysFromMonthStart]
Sales table
Measures
QtyDevelopment tells the total historical order qty cumulatively based on the date range
QtyDevelopment =
CALCULATE(
SUM(Sales[Qty]),
FILTER(
ALLSELECTED(Sales),
Sales[Range_RoundTo5] <= MAX(Sales[Range_RoundTo5])
)
)
AverageDevelopment tells the historical average of how much of total monthly orders are created by the particular date range
AverageDevelopment =
[QtyDevelopment] /
CALCULATE(
SUM(Sales[Qty]),
Ranges[DaysFromMonthStart] <= 30
)
OrderQtyNow tells the Qty we have received in the current month
OrderQTY_Now =
CALCULATE(
CALCULATE(
SUM(Sales[Qty]),
YEAR(Sales[DeliveryMonthStart]) = YEAR(TODAY())
),
FILTER( ALLSELECTED(Sales),
AND(
Sales[Range_RoundTo5] <= MAX(Ranges[DaysFromMonthStart]),
MONTH(Sales[DeliveryMonthStart]) = MONTH(TODAY())
)
)
)
OrdersVsForecast simply compares the received order qty with the total forecast for this month
OrdersVsForecast =
[OrderQTY_Now] / SUM(Forecast[ForecastQty])
Any help and hints are highly appreciated, been trying out many different ways but unfortuantely I did not manage to get any reasonable result!
Solved! Go to Solution.
Hi @Anonymous,
You can add variables to extract the current axis date and add if statement to your formula to compare with the current date and prevent the calculate on the date ranges which greater than the current date.
Then these formula results which larget than current date will be filtered as blank.
Measure =
VAR currAxis =
MAX ( Table[Date] )
RETURN
IF ( currAxis <= TODAY (), 'your formula', BLANK () )
Regards,
Xiaoxin Sheng
Got the StDev solved, but still would be interested how to make the "future" date ranges blank?
Hi @Anonymous,
You can add variables to extract the current axis date and add if statement to your formula to compare with the current date and prevent the calculate on the date ranges which greater than the current date.
Then these formula results which larget than current date will be filtered as blank.
Measure =
VAR currAxis =
MAX ( Table[Date] )
RETURN
IF ( currAxis <= TODAY (), 'your formula', BLANK () )
Regards,
Xiaoxin Sheng
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
77 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |