Measure DAX. Calculation of lost profits

[AleksandrG]

Hi all.

Please help me to calculate lost profits.

I need to calculate the lost profit only on the days when the product was out of stock.

It turns out that when the goods are not in stock, we make a calculation:

DIVIDE ( '_Measure'[Profit], '_Measure'[Sales Qty], 0 ) * '_Measure'[Sales per day].

I made a measure, but it doesn't display anything on the chart.

IF (
'_Measure'[Remainder] = 0,
DIVIDE ( '_Measure'[Profit], '_Measure'[Sales Qty], 0 ) * '_Measure'[Sales per day]
)

How can I modify the measure so that it works correctly?
Link to pbix example: https://drive.google.com/file/d/14HifmSoKSFJ-q3CEzx56QnNliO8wH-JV/view?usp=sharing
Screenshot_Example: https://drive.google.com/file/d/1DE2g3iXM-0TNgRABRFmbsBRxSYt4G5UD/view?usp=sharing

[johnt75 · Accepted solution]

You need to remove the date filters placed on the measures.

Lost profit =
IF (
    [Remainder] = 0,
    VAR Profit =
        CALCULATE ( [Profit], REMOVEFILTERS ( 'Date' ) )
    VAR SalesQty =
        CALCULATE ( [Sales Qty], REMOVEFILTERS ( 'Date' ) )
    VAR SalesPerDay =
        CALCULATE ( [Sales Per Day], REMOVEFILTERS ( 'Date' ) )
    RETURN
        DIVIDE ( Profit, SalesQty, 0 ) * SalesPerDay
)

[MFelix]

Hi @AleksandrG ,

When you have the remainder has 0 you do not have any values has you can see below:

So this calculation will give you blank values, how is this data getting pick up for that dates? Can you please explain a little better what you need to get?

[AleksandrG]

I wanted to calculate how much we could earn if the goods were in stock. : )

johnt75 gave the correct solution.

I still don't know how it will look in the finished product. But at this stage, this is what I need. Now I am implementing this into our dashboard and will continue to refine it.

[johnt75 · Accepted solution]

You need to remove the date filters placed on the measures.

Lost profit =
IF (
    [Remainder] = 0,
    VAR Profit =
        CALCULATE ( [Profit], REMOVEFILTERS ( 'Date' ) )
    VAR SalesQty =
        CALCULATE ( [Sales Qty], REMOVEFILTERS ( 'Date' ) )
    VAR SalesPerDay =
        CALCULATE ( [Sales Per Day], REMOVEFILTERS ( 'Date' ) )
    RETURN
        DIVIDE ( Profit, SalesQty, 0 ) * SalesPerDay
)

[AleksandrG]

But how to make it so that only the date period that we have chosen is taken into account in the calculation?

Now he looks at all the dates that are in the data and calculates the lost profit from them.
And our goal is to choose a period, and in it to calculate on the days when there is no goods in stock, how much we would earn if the goods were in stock.
It turns out that the calculation should take into account only the profit, the number of sales and sales per day that were in the selected period.

[johnt75]

You can replace each instance of REMOVEFILTERS with ALLSELECTED

[AleksandrG]

I tried two options, but they did not work. Chart doesn't show data if I start using ALLSELECTED or DATEBETWEEN

First option:

SUMX (

    VALUES ( Assortment ),

    IF (

        [Remainder] = 0,

        VAR Profit =

            CALCULATE ( [Profit], ALLSELECTED( 'Calendar'[Date]))

        VAR SalesQty =

            CALCULATE ( [Sales Qty], ALLSELECTED( 'Calendar'[Date]))

        VAR SalesPerDay =

            CALCULATE ( [Sales Per Day], ALLSELECTED( 'Calendar'[Date]))

        RETURN

            DIVIDE ( Profit, SalesQty, 0 ) * SalesPerDay

    )

)

Second option:

SUMX (

    VALUES ( Assortment ),

    IF (

        [Remainder] = 0,

        VAR Profit =

            CALCULATE ( [Profit], REMOVEFILTERS ( 'Calendar' ), DATESBETWEEN('Calendar'[Date],MIN('Calendar'[Date]),MAX('Calendar'[Date]) ))

        VAR SalesQty =

            CALCULATE ( [Sales Qty], REMOVEFILTERS ( 'Calendar' ), DATESBETWEEN('Calendar'[Date],MIN('Calendar'[Date]),MAX('Calendar'[Date]) ))

        VAR SalesPerDay =

            CALCULATE ( [Sales Per Day], REMOVEFILTERS ( 'Calendar' ), DATESBETWEEN('Calendar'[Date],MIN('Calendar'[Date]),MAX('Calendar'[Date]) ))

        RETURN

            DIVIDE ( Profit, SalesQty, 0 ) * SalesPerDay

    )

)

Individually, each VAR counts correctly if I use ALLSELECTED or DATEBETWEEN. But the data is no longer displayed on the graph.

What am I doing wrong?

[johnt75 · Accepted solution]

Lost profit = 
IF (
    [Remainder] = 0,
    VAR Profit =
        CALCULATE ( [Profit], ALLSELECTED ( 'Calendar' ) )
    VAR SalesQty =
        CALCULATE ( [Sales Qty], ALLSELECTED( 'Calendar' ) )
    VAR SalesPerDay =
        CALCULATE ( [Sales Per Day], ALLSELECTED( 'Calendar' ) )
    RETURN
        DIVIDE ( Profit, SalesQty, 0 ) * SalesPerDay
)

This shows a value for Lost Profit when there is 0 remainder.

[AleksandrG]

Now I understand. Here it was necessary to remove the reference to the [date] column in ALLSELECTED, leaving only Calendar.

Only now I can’t understand why he gives out one value to the extent -
3,043.83, and in case of manual calculation - 3,052.56

[johnt75]

that's rounding. the actual value for Sales per day is not 0.26, it is 0.259259 recurring. if you want the lost profit to match the figures shown to 2 decimal places you will need to change the base measures to round the result before returning it.

[AleksandrG]

Hello again)
Help please to understand here still with takyo the task.
I want to improve our measure so that it can be filtered through a parameter.
I want to make the calculation of this measure occur only for products that have a % profit greater than specified in the parameter.
I created a numerical range through the "Create parameter" function, in which I specified values ​​​​from 0.00 to 0.95. And upgraded our measure.

Lost profit_2 =

VAR _1 =

    ADDCOLUMNS(  Assortment4, "Percent", [% Profit] )

RETURN

SUMX (

    FILTER (

        _1,

        [Percent] > '% Profit'[Значение % Profit]),

    IF (

        [Remainder] = 0,

        VAR Profit =

            CALCULATE ( [Profit], ALLSELECTED ( 'Calendar' ) )

        VAR SalesQty =

            CALCULATE ( [Sales Qty], ALLSELECTED ( 'Calendar' ) )

        VAR SalesPerDay =

            CALCULATE ( [Sales Per Day], ALLSELECTED ( 'Calendar' ) )

        RETURN

            DIVIDE ( Profit, SalesQty, 0 ) * SalesPerDay

    )

)

But this measure doesn't work. SUMX does not see the table in the first argument, which I pass to it using the FULTER function.
Can you please tell me how to fix this so that it works?

File example: https://drive.google.com/file/d/13s7xmAkL4rPif0--kaVHYSYHf0Cu7Mx1/view?usp=sharing