Date diff

[gauran]

Hi
I want to mimic the formula in power bi from Excel. But not too sure how to do this. the main formula used in Excel in column to DUE is =IF([@PromiseDt]=0,"Uncommitted",[@PromiseDt]-MAX(TODAY(),[@DueDate]))

DueDate	First Promise	PromiseDt	to Due
27-Jan-2022			Uncommitted
01-Feb-2022	22-Apr-2022	22-Apr-2022	-5.00
15-Mar-2022	22-Apr-2022	22-Apr-2022	-5.00
14-Feb-2022	22-Apr-2022	22-Apr-2022	-5.00
17-Apr-2022	06-May-2022	06-May-2022	9.00
08-Aug-2022	08-Aug-2022	08-Aug-2022	0.00
12-Jul-2022	12-Jul-2022	12-Jul-2022	0.00
11-Apr-2022	22-Apr-2022	22-Apr-2022	-5.00
20-Sep-2022	20-Sep-2022	20-Sep-2022	0.00
18-Oct-2022	18-Oct-2022	18-Oct-2022	0.00
17-Mar-2023	17-Mar-2023	17-Mar-2023	0.00
11-Jan-2023	11-Jan-2023	11-Jan-2023	0.00
12-Oct-2022	27-Apr-2022	27-Apr-2022	-168.00
12-Oct-2022	12-Oct-2022	12-Oct-2022	0.00
08-Aug-2022	08-Aug-2022	08-Aug-2022	0.00
21-Sep-2022	21-Sep-2022	21-Sep-2022	0.00
31-May-2022	31-May-2022	31-May-2022	0.00
25-Apr-2022	25-Apr-2022	25-Apr-2022	-2.00
21-Jun-2022	21-Jun-2022	21-Jun-2022	0.00
12-Jul-2022	12-Jul-2022	12-Jul-2022	0.00
25-Apr-2022	25-Apr-2022	25-Apr-2022	-2.00
25-Apr-2022	25-Apr-2022	25-Apr-2022	-2.00
17-Apr-2022	18-May-2022	18-May-2022	21.00
20-May-2022	25-May-2022	25-May-2022	5.00
14-Jun-2022	14-Jun-2022	14-Jun-2022	0.00
28-Jun-2022	28-Jun-2022	28-Jun-2022	0.00
26-Aug-2022	26-Aug-2022	26-Aug-2022	0.00
27-Apr-2022	31-May-2022	31-May-2022	34.00
07-Sep-2022	07-Sep-2022	07-Sep-2022	0.00
07-Jun-2022	12-Jul-2022	12-Jul-2022	35.00

 

[Chihiro]

Personally, I'd recommend doing calculation in M code (power query) stage. About 90% of calculated columns are more efficient in PQ when compared to DAX calculated columns (especially when it's within single table context).

Also, I'm not a fan of mixing numeric and text values in single column. This will add unnecessary overhead during subsequent analysis/calculation.

M code for custom column.

= if [PromiseDt] = null then null else Duration.Days([PromiseDt] - List.Max({DateTime.Date(DateTime.FixedLocalNow()),[DueDate]}))