Delta Measure showing wrong number ONLY when no data available

[dyttam]

Hello, 

I have created a measure that will calculate the DELTA of a dynamic date table. 

I am having a problem only when there is no data for a month in between the selected months. When there is no data for a month within the range of the MIN/MAX values, the number value is flipped (positive instead of negative, negative instead of positive).

For example, If we pick June and August to compare the Delta, using the following DAX formula (see below), we will get a value of 30.87. The value should be -30.87. Negative, not positive. This only happens when there is no data in a month in between the date selection. If I were to select August and September, or August and October, (or any months that do not have a data lapse, I receive the proper amount showing the proper negative or positive value. 

Thoughts?

Here's the DAX I am using and two pictures. One, showing the value as a positive number (incorrect) and one showing the value as a negative numer (correct). The only difference is that July has no data... and as a result, inverts the integer for some reason.

Change Used Space =

Var MINDATE = MIN(_Date[MonthNameLong])

Var MAXDATE = MAX(_Date[MonthNameLong])

Var MINAVAIL = CALCULATE([Used Space], FILTER(_Date, _Date[MonthNameLong] = MINDATE))

Var MAXAVAIL = CALCULATE([Used Space], FILTER(_Date, _Date[MonthNameLong] = MAXDATE))

return

MINAVAIL - MAXAVAIL

[DataZoe · Accepted solution]

@dyttam the issue is it's giving the min/max by a text value, which means it's going to be alphabetically for your months, no in the calendar order.

I would suggest adding a month column that is an integer, so that it will pick the month ordering instead.

this calculated column, add to your _Date table:

YYYYMM =
VALUE ( FORMAT ( [Date],"yyyyMM" ) )

then modify your formula:

Change Used Space =
VAR MINDATE =
    MIN ( _Date[YYYYMM] )
VAR MAXDATE =
    MAX ( _Date[YYYYMM] )
VAR MINAVAIL =
    CALCULATE ( [Used Space], FILTER ( _Date, _Date[YYYYMM] = MINDATE ) )
VAR MAXAVAIL =
    CALCULATE ( [Used Space], FILTER ( _Date, _Date[YYYYMM] = MAXDATE ) )
RETURN
    MINAVAIL - MAXAVAIL

[dyttam]

@DataZoe  Thank you for the response. I've added a month column that is an integer to my Date table and adjusted the DAX accordingly. This looks to be working as intended. Cheers!

[DataZoe]

@dyttam Awesome, glad to hear that it's working now!