DAX Date Column Not Working

[Anonymous]

Hello. I am trying to create a calculated column with DAX using if statements. I created a measure that works and outputs what I want it to but when I cannot put this measure into the chart as it does not let me. I tried creating a DAX column instead and it outputs a single date for some reason and I am not sure why. How would I use this calculation in a visual other than a table and would I be able to use it in a column? One note is that I am using 2 excel files with a relationship. Not sure if that would affect it.

-> measure that works correctly output into table

This is the measure I created.

-> Same measure used as a calculated column instead

[v-yiruan-msft]

Hi @Anonymous ,

As checked the formula of your measure, it involves two tables('Project_Number_and_Deadline' and 'forecast_and_actuals_data') and multiples date fields. How did you create the relationship between these two tables? Which table you are creating the calculated column in? Could you please provide some raw data of these two tables and the logic of [calculation date]? Then we can give you a suitable solution later... Thank you.

Best Regards

[Anonymous]

The relationship is many to many. I am connecting project numbers to each other to get the deadline date but the 'Project Number and Deadline' has more project numbers than the other document. Forecast and Actuals is where the main data is coming from. I will share some data to give you an idea. I tried to create the column in both tables but neither worked. It works as a measure in table visualization but I cannot use it in a graph, which is what I need it for.

Due Date = IF(MAX('Project_Number_and_Deadline'[Deadline])>(TODAY()),MAX(Project_Number_and_Deadline[Deadline]),IF(MAX('Project_Number_and_Deadline'[Deadline])>(TODAY()-45),TODAY(),IF(MAX('forecast_and_actuals_data'[Promise Date])<>BLANK() && MAX(forecast_and_actuals_data[Promise Date])>TODAY(),MAX('forecast_and_actuals_data'[Promise Date]),IF(MAX('forecast_and_actuals_data'[Scheduled Ship Date])>(TODAY()) && MAX(forecast_and_actuals_data[Scheduled Ship Date])<(TODAY()+60),MAX(forecast_and_actuals_data[Scheduled Ship Date]),IF(MAX('forecast_and_actuals_data'[Scheduled Ship Date])>(TODAY()+60) && MAX(forecast_and_actuals_data[Scheduled Ship Date])<(TODAY()+120),(TODAY()+60),TODAY()+90)))))

Here is some sample data:

-> forecast and actuals-> project number and deadline

[HoangHugo]

You use MAX[column], it will return only one value, so it lead to your formula = same value. I did not know your data, and purpose, so can not support detail.

[Anonymous]

Hello. I am providing more info in the comment above.