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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
dianecrz
Frequent Visitor

DAX Delta Issue - Difference Between Dates Not Working When Amount is Zero for Maximum Date Selected

I am trying to accurately calculate the difference in the amount sold between the most recent date selected and the oldest one.

I used a DAX formula to create my Delta, and when calculating the difference between dates, my Delta works for most scenarios. However, there are cases where the measure is not working, specifically when we have 0 for the maximum date and a positive value > 0 for the minimum date.  

 

Here is the DAX I am using for my Delta, in my Measure:

 

 

Diff = 
IF (
    HASONEVALUE ( Table[Snapshot_Date] ),
    ( ( SUM(Table[Orders]) ) ),
    CALCULATE (
            ( SUM(Table[Orders])),
            FILTER (
                Table,
                Table[Snapshot_Date] = MIN ( ( Table[Snapshot_Date] ) )
            )
    ) 
        - CALCULATE (
        ( SUM(Table[Orders])),
        FILTER (
            Table,
            Table[Snapshot_Date] = MAX ( ( Table[Snapshot_Date] ) )
        )
    )
        ) ​

 

 

 

The screenshot below shows that my Delta works perfectly when calculating the difference for the selected dates and rows below.

 

DeltaWorking.png

 

 

 

 

 

Here follows one example where my measure is not working, where I have 0 for the maximum date and a positive value > 0 for the minimum date:

 

Delta_Issue_PBI.png




For the highlighted row, the delta should be -0.1 instead.
All other rows in this matrix are making sense. 

Can someone please help me out with this issue? 

Thank you 🐾

 

3 REPLIES 3
dianecrz
Frequent Visitor

Hi @Martin_D 

The solution you provided solves the delta issue when the latest date value is blank. The screenshot below shows the results of your DAX function. 

NewDiff_Screenshot.PNG

However, it is not ideal because it converts the minimum date values to negative, which works fine for the delta calculation but can be wrongly interpreted by users, since these are positive orders from July 5th. 

 

I am considering as a second option to work on the data and insert values for dates where I do not have any order data. Please let me know if there is an easier solution. 

Thank you so much!!!

Best, 

Diane

 

Hi @dianecrz ,

Would you like to provide a sample file? As long as the dates in the column headers come from Table[Snapshot Date] the measures shouldn't impact the sign (_Sign factor is one in this case).
BR
Martin

Martin_D
Super User
Super User

Hi dianecrz,

in the context of the highlighted row, 17-May-23 is your min date because there are no orders on 29-May-22 in the context of this row. The yellow line executes the first calcualtion of your IF statement (Table[Snapshot_Date] has one value], even in the context of the Delta column).
You could try adding another IF to invert the sign if the row has only one date with orders an it's not the max date in the context, like:

Diff = 
VAR _Sign =
    IF (
        ISISNCOPE ( Table[Snapshot_Date] ),
        1,
        VAR _MaxRowDate = MAX ( Table[Snapshot_Date] )
        VAR _MaxVisualDate =
            CALCULATE (
                MAX ( Table[Snapshot_Date] ),
                ALLSELECTED ()
            )
        RETURN
        IF (
            _MaxRowDate = _MaxVisualDate,
            -1,
            1
        )
    )
VAR _Diff = 
    IF (
        HASONEVALUE ( Table[Snapshot_Date] ),
        SUM ( Table[Orders] ),
        CALCULATE (
            SUM( Table[Orders] ),
            FILTER (
                Table,
                Table[Snapshot_Date] = MIN ( Table[Snapshot_Date] )
            )
        ) 
        - CALCULATE (
            SUM ( Table[Orders] ),
            FILTER (
                Table,
                Table[Snapshot_Date] = MAX ( Table[Snapshot_Date] )
            )
        )
    )
RETURN
_Sign * _Diff

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors