Unable to create measure with DAX because my columns are not recognized.

[Yohana]

From my DATA I have many columns with dates and null values. I want to use DATEDIFF so I can have the days betweens some columns, however when I use DAX it does not recognize my columns. The dates are in the correct format, I tried Co-pilot and the solutions does not apply.  I really need some help to figure out how to fix it...

Tese are the columns:

DAX does not find the column...

[dharmendars007]

Hello @Yohana , 

You can try the below measure since you are mentioning you have null values in the table you are referring too, if the silution dont work , please share us the sample data which will help us to deep dive into the issue.

Days_TSS_to_PM =
IF(ISBLANK([T/S Week Ending]) || ISBLANK([TS Submitted to the PM]),
BLANK(),DATEDIFF([T/S Week Ending], [TS Submitted to the PM], DAY))

If you find this helpful , please mark it as solution and Your Kudos/Likes are much appreciated!

Thank You

Dharmendar S

LinkedIN 

[Yohana]

Hello @dharmendars007 ,

I tried to use that earlier, I did it again, but the problem is the value of the column cannot be determined...Maybe something is wrong with the column but I don't know how to solve this...

[Anonymous]

Thanks for the reply from dharmendars007  and bhanu_gautam  please allow me to provide another insight:

Hi, @Yohana 

Regarding the issue you raised, my solution is as follows:

You can try entering a single quotation mark (') where you need the column name. This will trigger the intelligent suggestions, as shown in the image below:

Then, you simply need to select the desired content, and the final result will be as follows:

Using the left square bracket [ is also possible, but it primarily triggers intelligent suggestions for measures rather than column names.

Of course, if you have any new ideas, you are welcome to contact us.
 

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Yohana]

I apprecciate your reply, but it did not work.

[Anonymous · Accepted solution]

Hi, @Yohana 

Thank you for your swift response.
Regarding the issue you raised, my solution is as follows:

1.If you are using a measure, you need to wrap the first two parameters of the DATEDIFF() function with MAX() or SUM() to ensure it returns the current row value.

Measure = DATEDIFF(MAX('Table'[T/S Week Ending]),MAX('Table'[TS Submitted to the PM (Date)]),DAY)

Measure 2 = DATEDIFF(SUM('Table'[T/S Week Ending]),SUM('Table'[TS Submitted to the PM (Date)]),DAY)

2.If you are using a calculated column, this is not necessary:

Column = DATEDIFF('Table'[T/S Week Ending],'Table'[TS Submitted to the PM (Date)],DAY)

3.Here is the final result, which I hope meets your expectations:

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Yohana]

T/S Week Ending TS Submitted to the PM (Date)
01-May-21 10-Jun-21
08-May-21 10-Jun-21
15-May-21 10-Jun-21
22-May-21 10-Jun-21
29-May-21 10-Jun-21
05-Jun-21 8-Jul-21
12-Jun-21 8-Jul-21
19-Jun-21 8-Jul-21
26-Jun-21 8-Jul-21
03-Jul-21 22-Jul-21
03-Jul-21 12-Aug-21
10-Jul-21 22-Jul-21
10-Jul-21 12-Aug-21
17-Jul-21 22-Jul-21
17-Jul-21 12-Aug-21
24-Jul-21 26-Aug-21
31-Jul-21 26-Aug-21
07-Aug-21 26-Aug-21
14-Aug-21 26-Aug-21
14-Aug-21 25-Nov-21
14-Aug-21 26-Aug-21
21-Aug-21 9-Sep-21
28-Aug-21 25-Nov-21
28-Aug-21 25-Nov-21
04-Sep-21 25-Nov-21
04-Sep-21 25-Nov-21
11-Sep-21 25-Nov-21
11-Sep-21 25-Nov-21
18-Sep-21 25-Nov-21
25-Sep-21 26-Nov-21
25-Sep-21 21-Nov-21

[Yohana]

I wish I could send as a table, but it keeps giving me errors because is HTML, but mainly are 2 columns from the excel file.

also, 

one thing happens too, Power BI does not show a little calendar in front of one of the columns T/S Week Ending

[bhanu_gautam]

@Yohana , Can you share some example or sample data