Forum Discussion

ZEB's avatar
ZEB
Frequent Visitor
2 years ago
Solved

Inconsistent Results with DAX Calculated Column

Hi all, I am having trouble figuring out why the following calculated column doesn't work for a few rows while it works perfectly for the remaining ones: PowerBI and Excel File attachment link Est...
Need Help
  • ZEB's avatar
    ZEB
    2 years ago

    I have figured out the issue, I had to apply a MAXX() in my filter clause.

     

    Here's the udpated DAX

     

    EstDateChange__ = 
VAR CurrentContract = 'Table'[Contract Number]
VAR CurrentDate = 'Table'[Snapshot Date]
VAR CurrentEstDate = 'Table'[Est. Close Date]
VAR PreviousEstDate =
    CALCULATE(
        MAX('Table'[Est. Close Date]),
        FILTER(
            'Table',
            'Table'[Contract Number] = CurrentContract &&
            'Table'[Snapshot Date] = 
                MAXX(
                    FILTER(
                        'Table',
                        'Table'[Contract Number] = CurrentContract &&
                        'Table'[Snapshot Date] < CurrentDate
                    ),
                    'Table'[Snapshot Date]
                )
        )
    )
VAR IsFirstRow =
    CALCULATE(
        COUNTROWS('Table'),
        FILTER(
            'Table',
            'Table'[Contract Number] = CurrentContract &&
            'Table'[Snapshot Date] < CurrentDate
        )
    ) = 0
RETURN
IF(IsFirstRow, 0, IF(CurrentEstDate = PreviousEstDate, 0, 1))

     

ZEB's avatar
ZEB
Frequent Visitor
2 years ago

I have figured out the issue, I had to apply a MAXX() in my filter clause.

 

Here's the udpated DAX

 

EstDateChange__ = 
VAR CurrentContract = 'Table'[Contract Number]
VAR CurrentDate = 'Table'[Snapshot Date]
VAR CurrentEstDate = 'Table'[Est. Close Date]
VAR PreviousEstDate =
    CALCULATE(
        MAX('Table'[Est. Close Date]),
        FILTER(
            'Table',
            'Table'[Contract Number] = CurrentContract &&
            'Table'[Snapshot Date] = 
                MAXX(
                    FILTER(
                        'Table',
                        'Table'[Contract Number] = CurrentContract &&
                        'Table'[Snapshot Date] < CurrentDate
                    ),
                    'Table'[Snapshot Date]
                )
        )
    )
VAR IsFirstRow =
    CALCULATE(
        COUNTROWS('Table'),
        FILTER(
            'Table',
            'Table'[Contract Number] = CurrentContract &&
            'Table'[Snapshot Date] < CurrentDate
        )
    ) = 0
RETURN
IF(IsFirstRow, 0, IF(CurrentEstDate = PreviousEstDate, 0, 1))