Adding a calculated date column that adds 5 days to my due date - getting errors

[Disaster110]

Hi Team,

I have been trying to add a new column to my model that contains dates that are five days after my due date column.

I have been abe to create the new column, and the dates are corect, but when I try to add it ti may visual (Table) I keep getting the error message "Relationship or measure is not in a valid state"

I used this DAX =DATEADD(Due_Date, 7, DAY) and = ([Due_date] + 5) both created the new column, wth correct dates but I cannot add the variable to my visuals.

All other variables, relationships, and measures are working fine accross the model.

Any thoughts?

[Ritaf1983]

Hi @Disaster110 
This is something strange in your formula with "and"...

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[Disaster110]

Sorry, my mistake, I meant to say -

I used both of these formulae seperately and both worked to create the column -

First DAX formula =DATEADD(Due_Date, 5, DAY)

Second DAX formula = ([Due_date] + 5

Will post sample data shortly 🙂

[Disaster110]

Scratch that, My organisation will not let me share or post any of the data as it contains sensative info that cant be scrubbed.

Here is a screen shot of the error message if it helps - 

[Ritaf1983]

Hi @Disaster110 
I don't need your real data, I just need some sample table to use to understand what data you have and what is the desired result...

[Disaster110]

Task_CodeDue_DateFormatted_Application_NumberFormatted_LocationColumn1

DAPEAAP	11/07/2023 0:00	EXA/2023/18	30 Seventh Avenue PALM BEACH  QLD  4221
DAPEAAP	11/07/2023 0:00	MCU/2022/516	22 The Panorama TALLAI  QLD  4213
DAPEAAP	11/07/2023 0:00	MCU/2023/18	50 Kingston Drive HELENSVALE  QLD  4212
DAPEAAP	11/07/2023 0:00	MIN/2023/152	2-6 Greenwich Court ROBINA  QLD  4226
DAPEAAP	11/07/2023 0:00	MIN/2023/192	378 Marine Parade LABRADOR  QLD  4215
DAPEAAP	11/07/2023 0:00	MIN/2023/192	UNIT 704 378 Marine Parade LABRADOR  QLD  4215
DAPEAAP	11/07/2023 0:00	MIN/2023/99	4 Musgrave Street COOLANGATTA  QLD  4225
DAPEAAP	11/07/2023 0:00	OPV/2023/192	4 Creal Court CURRUMBIN WATERS  QLD  4223
DAPEAAP	11/07/2023 0:00	OPV/2023/207	12 Bower Court BURLEIGH HEADS  QLD  4220
DAPEAAP	11/07/2023 0:00	OPV/2023/222	88 Acanthus Avenue BURLEIGH HEADS  QLD  4220
DAPEAAP	11/07/2023 0:00	OPV/2023/53	9 Aliciajay Circuit LUSCOMBE  QLD  4207
DAPEAAP	11/07/2023 0:00	OPW/2023/381	9 Aliciajay Circuit LUSCOMBE  QLD  4207
DAPEAAP	11/07/2023 0:00	OPW/2023/422	382 Tamborine Oxenford Road UPPER COOMERA  QLD  4209
DAPEAAP	11/07/2023 0:00	OPW/2023/577	31 Reserve Road UPPER COOMERA  QLD  4209
DAPEAAP	11/07/2023 0:00	ROL/2023/31	203 Baileys Mountain Road UPPER COOMERA  QLD  4209
DAPEAAP	12/07/2023 0:00	COM/2022/362	77 Darlington Drive YATALA  QLD  4207
DAPEAAP	12/07/2023 0:00	COM/2023/20	7 Zupp Drive ORMEAU  QLD  4208
DAPEAAP	12/07/2023 0:00	MCU/2022/465	25 Vatakoula Parade MERMAID WATERS  QLD  4218
DAPEAAP	12/07/2023 0:00	MCU/2022/614	10 Ern Harley Drive BURLEIGH HEADS  QLD  4220
DAPEAAP	12/07/2023 0:00	MCU/2023/87	173-175 Surf Parade BROADBEACH  QLD  4218
DAPEAAP	12/07/2023 0:00	MCU/2023/87	UNIT 1 173-175 Surf Parade BROADBEACH  QLD  4218
DAPEAAP	12/07/2023 0:00	MCU/2023/87	UNIT 2 173-175 Surf Parade BROADBEACH  QLD  4218
DAPEAAP	12/07/2023 0:00	MCU/2023/87	UNIT 3 173-175 Surf Parade BROADBEACH  QLD  4218
DAPEAAP	12/07/2023 0:00	MCU/2023/87	UNIT 4 173-175 Surf Parade BROADBEACH  QLD  4218
DAPEAAP	12/07/2023 0:00	MCU/2023/87	UNIT 5 173-175 Surf Parade BROADBEACH  QLD  4218
DAPEAAP	12/07/2023 0:00	MCU/2023/87	UNIT 6 173-175 Surf Parade BROADBEACH  QLD  4218
DAPEAAP	12/07/2023 0:00	MIN/2023/148	313 Golden Four Drive BILINGA  QLD  4225
DAPEAAP	12/07/2023 0:00	MIN/2023/257	119 Musgrave Street COOLANGATTA  QLD  4225
DAPEAAP	12/07/2023 0:00	MIN/2023/257	2-18 Marine Parade COOLANGATTA  QLD  4225

[Disaster110 · Accepted solution]

Thanks for your help @Ritaf1983 ,

I found a solution. I had to create a date hierarchy for the new calculated date column, then it worked without the error.