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))

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi, ZEB 
Can you elaborate on your logic? I don't understand, for example ken is 5 in your data, but 2 in your expectation

How do they change? And how the EstDateChange column changes into the data you want.

 

Best Regards

Yongkang Hua

ZEB's avatar
ZEB
Frequent Visitor
2 years ago

Thank you Anonymous for looking into it,

Here is the screenshot of Expected Vs Actual. Ken's Expected and Actual EstDateChange are same, but there is a discrepancy for his RevenueChange. 

The logic is similar to the RANK function's `PARTITION BY` and `ORDER BY` clauses in SQL.
For `EstCloseDateChange`, my `PARTITION BY` includes two columns: `EstCloseDate` and `ContractNumber`, and I am ordering by `SnapshotDate` in ascending order.
For `RevenueChange`, my `PARTITION BY` includes `Revenue` and `ContractNumber`, and I am also ordering by `SnapshotDate` in ascending order.

Essentially, I am checking how many times the estimated close date and revenue have changed for a contract number across various snapshot dates. I am also trying to return the last estimated close date and revenue for the latest snapshot date, as well as the values of these two fields before they changed for the last time.

I have also edited my orginal post to explain the calculation logic better.

Let me know if you need any more further details