Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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
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
Solved! Go to Solution.
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))
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))
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
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.
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
User | Count |
---|---|
20 | |
17 | |
15 | |
11 | |
7 |
User | Count |
---|---|
28 | |
27 | |
13 | |
12 | |
12 |