cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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

Capture.JPGCapture1.JPG

 
 

 




1 ACCEPTED SOLUTION
Super User II
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.

 

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

 

Respectfully,
DataZoe

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

View solution in original post

3 REPLIES 3
Super User II
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.

 

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

 

Respectfully,
DataZoe

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

View solution in original post

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

 

 

 



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

Respectfully,
DataZoe

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

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

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

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors