Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dyttam
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
DataZoe
Employee
Employee

@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,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

View solution in original post

3 REPLIES 3
DataZoe
Employee
Employee

@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,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

@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,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.