Return min date where the measure does not equal to blank

[MichaelStrauss]

Hi All, 

I have a measure that calculates the absolute change of another measure between the MIN & MAX selected date.

The problem is that once the other measure returns blank for the MIN date/MAX date , the absolute change calculation is not what I expect to get. In such cases I would like to enforce my measure to take the value of the first date that does not evaluate to BLANK (in case of MIN date), or take the last date that does not evaluate to BLANK (in case of MAX date). How can I do that using DAX?

Stock DIO Change =

VAR MAX_DATE = CALCULATE(MAX(Fact_Stock[Period Date]),ALLSELECTED(Fact_Stock[Period Date]))

VAR MIN_DATE = CALCULATE(MIN(Fact_Stock[Period Date]),ALLSELECTED(Fact_Stock[Period Date]))

RETURN ABS(CALCULATE('Measures Table'[Stock Value DIO],Fact_Stock[Period Date] = MAX_DATE) -

    CALCULATE('Measures Table'[Stock Value DIO],Fact_Stock[Period Date] = MIN_DATE))

[v-yueyunzh-msft]

Hi, @MichaelStrauss 

Here are the steps you can refer to :

(1)This is my test data:

(2)We can create a measure :

Measure = var _max_slice = MAX('Sheet1'[Date])
var _min_slice = MIN('Sheet1'[Date])
var _table  =  FILTER(ALL('Sheet1') , 'Sheet1'[Date] >= _min_slice && 'Sheet1'[Date] <=_max_slice && 'Sheet1'[Value] <> BLANK())
var _min_date = MINX(_table , [Date])
var _max_date  =MAXX(_table , [Date])
var _first_value =SUMX( FILTER( ALL('Sheet1') , 'Sheet1'[Date]= _min_date) , [Value])
var _last_value = SUMX( FILTER( ALL('Sheet1') , 'Sheet1'[Date]= _max_date) , [Value])
return 
ABS(_last_value-_first_value)

(3)Then we can meet your need , the result is as follows:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

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

[MichaelStrauss]

Hi @v-yueyunzh-msft

Thank you for your answer. It did give me a solution to the absolute stock-change measure. That question that arises, what if I have a more complicated measure that is using multiple columns and also some time-intelligenece formula? Then I'm not able to use the same logic as in the VAR _table, I can't put a certain column that will not be equal to blank.

Here is my 'complicated' measure:

Stock Value DIO =

VAR Selected_Month_Slicer = SWITCH(

    TRUE(),

    [Selected_Month_Slicer] = "3", 3,

    [Selected_Month_Slicer] = "6", 6,

    [Selected_Month_Slicer] = "9", 9,

    [Selected_Month_Slicer] = "12", 12 )

     -- This is a parameter

VAR Selected_Month_Days = SWITCH(

    TRUE(),

    [Selected_Month_Slicer] = "3", 90,

    [Selected_Month_Slicer] = "6", 180,

    [Selected_Month_Slicer] = "9", 270,

    [Selected_Month_Slicer] = "12", 365)

    -- This is a parameter

VAR DIO =

    (

        SUMX(Fact_Stock,Fact_Stock[Stock Value])

/  CALCULATE( SUMX(Fact_Stock,Fact_Stock[Issue Total Stock Value]) ,DATESINPERIOD( Fact_Stock[Period Date].[Date] , MIN(Fact_Stock[Period Date]) , -Selected_Month_Slicer ,MONTH))) * Selected_Month_Days

RETURN IFERROR( DIO , BLANK())

So basically I would like to calculate the change of this measure between the Max selected date and the Min selected date, but as requested if one of them avaluates to BLANK, I want it to take the nearest month where the measure does not evaluate to BLANK

My Data-Set looks like this:

Sku No	Period Date	Stock value	Issue total stock value
1	01/01/2021
2	01/01/2021
3	01/01/2021
1	01/02/2021
2	01/02/2021
3	01/02/2021
1	01/03/2021
2	01/03/2021
3	01/03/2021
1	01/04/2021
2	01/04/2021
3	01/04/2021
1	01/05/2021
2	01/05/2021
3	01/05/2021