cancel
Showing results for
Did you mean:
Regular Visitor

Delta Measure showing wrong number ONLY when no data available

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

1 ACCEPTED SOLUTION
Super User II

@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.

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

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

Respectfully,
DataZoe

See my reports and blog at https://www.datazoepowerbi.com/

3 REPLIES 3
Super User II

@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.

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

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

Respectfully,
DataZoe

See my reports and blog at https://www.datazoepowerbi.com/

Regular Visitor

@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!

Super User II

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

Respectfully,
DataZoe

See my reports and blog at https://www.datazoepowerbi.com/

Announcements

Happy New Year from Power BI

This is a must watch for a message from Power BI!