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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ZEB
Frequent Visitor

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

EstDateChange = 
VAR CurrentContract = 'Table'[Contract Number]
VAR CurrentDate = 'Table'[Snapshot Date]
VAR CurrentEstDate = 'Table'[Est. Close Date]
VAR PreviousEstCloseDate =
    CALCULATE(
        MAX('Table'[Est. Close Date]),
        FILTER(
            'Table',
            'Table'[Contract Number] = CurrentContract &&
            'Table'[Snapshot Date] < CurrentDate
        )
    )
RETURN
IF(
    ISBLANK(PreviousEstCloseDate),
    0,
    IF(CurrentEstDate <> PreviousEstCloseDate, 1, 0)
)

Calculated column 'EstDateChange' groups by 'Contract Number' and 'Est.Close Date' and flags 1 if the 'Est.Close Date' has changed for a contract number, ordering by 'Snapshot Dates'. Similarly, the 'RevenueChanged' column groups by 'Revenue' and 'Contract Number' and flags 1 if the 'Revenue' has changed for any 'Snapshot Date' sorted in ascending order.

The following screenshot shows discrepancies between the expected and actual results

 

ZEB_0-1718271320631.png

Weirdly, it works for most of the rows but not for some. I have been trying hard to debug the issue but couldn't find what is wrong. Could anyone help me identify and fix the issue with this measure?

I have also attached an Excel file with the expected output calculated manually.

PowerBI and Excel File attachment link

 

Thank you!

@Ashish_Mathur @amitchandak @lbendlin@Ritaf1983 

 

1 ACCEPTED SOLUTION
ZEB
Frequent Visitor

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

 

View solution in original post

3 REPLIES 3
ZEB
Frequent Visitor

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

 

v-yohua-msft
Community Support
Community Support

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

vyohuamsft_0-1718259310336.png

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

 

Best Regards

Yongkang Hua

Thank you @v-yohua-msft 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. 

ZEB_0-1718266586607.png

ZEB_1-1718267636261.png

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors