Blank Rows and Standard Deviation for a Calculated Measure

[Anonymous]

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]

Relationships

Sales table

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!

[Anonymous]

Got the StDev solved, but still would be interested how to make the "future" date ranges blank?

[v-shex-msft · Accepted 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