Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Disaster110
Helper I
Helper I

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

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?

1 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.

View solution in original post

6 REPLIES 6
Ritaf1983
Super User
Super User

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

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 🙂

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 - 

Disaster110_0-1689043483564.png

 

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...

Task_CodeDue_DateFormatted_Application_NumberFormatted_LocationColumn1
DAPEAAP11/07/2023 0:00EXA/2023/1830 Seventh Avenue PALM BEACH  QLD  4221
DAPEAAP11/07/2023 0:00MCU/2022/51622 The Panorama TALLAI  QLD  4213
DAPEAAP11/07/2023 0:00MCU/2023/1850 Kingston Drive HELENSVALE  QLD  4212
DAPEAAP11/07/2023 0:00MIN/2023/1522-6 Greenwich Court ROBINA  QLD  4226
DAPEAAP11/07/2023 0:00MIN/2023/192378 Marine Parade LABRADOR  QLD  4215
DAPEAAP11/07/2023 0:00MIN/2023/192UNIT 704 378 Marine Parade LABRADOR  QLD  4215
DAPEAAP11/07/2023 0:00MIN/2023/994 Musgrave Street COOLANGATTA  QLD  4225
DAPEAAP11/07/2023 0:00OPV/2023/1924 Creal Court CURRUMBIN WATERS  QLD  4223
DAPEAAP11/07/2023 0:00OPV/2023/20712 Bower Court BURLEIGH HEADS  QLD  4220
DAPEAAP11/07/2023 0:00OPV/2023/22288 Acanthus Avenue BURLEIGH HEADS  QLD  4220
DAPEAAP11/07/2023 0:00OPV/2023/539 Aliciajay Circuit LUSCOMBE  QLD  4207
DAPEAAP11/07/2023 0:00OPW/2023/3819 Aliciajay Circuit LUSCOMBE  QLD  4207
DAPEAAP11/07/2023 0:00OPW/2023/422382 Tamborine Oxenford Road UPPER COOMERA  QLD  4209
DAPEAAP11/07/2023 0:00OPW/2023/57731 Reserve Road UPPER COOMERA  QLD  4209
DAPEAAP11/07/2023 0:00ROL/2023/31203 Baileys Mountain Road UPPER COOMERA  QLD  4209
DAPEAAP12/07/2023 0:00COM/2022/36277 Darlington Drive YATALA  QLD  4207
DAPEAAP12/07/2023 0:00COM/2023/207 Zupp Drive ORMEAU  QLD  4208
DAPEAAP12/07/2023 0:00MCU/2022/46525 Vatakoula Parade MERMAID WATERS  QLD  4218
DAPEAAP12/07/2023 0:00MCU/2022/61410 Ern Harley Drive BURLEIGH HEADS  QLD  4220
DAPEAAP12/07/2023 0:00MCU/2023/87173-175 Surf Parade BROADBEACH  QLD  4218
DAPEAAP12/07/2023 0:00MCU/2023/87UNIT 1 173-175 Surf Parade BROADBEACH  QLD  4218
DAPEAAP12/07/2023 0:00MCU/2023/87UNIT 2 173-175 Surf Parade BROADBEACH  QLD  4218
DAPEAAP12/07/2023 0:00MCU/2023/87UNIT 3 173-175 Surf Parade BROADBEACH  QLD  4218
DAPEAAP12/07/2023 0:00MCU/2023/87UNIT 4 173-175 Surf Parade BROADBEACH  QLD  4218
DAPEAAP12/07/2023 0:00MCU/2023/87UNIT 5 173-175 Surf Parade BROADBEACH  QLD  4218
DAPEAAP12/07/2023 0:00MCU/2023/87UNIT 6 173-175 Surf Parade BROADBEACH  QLD  4218
DAPEAAP12/07/2023 0:00MIN/2023/148313 Golden Four Drive BILINGA  QLD  4225
DAPEAAP12/07/2023 0:00MIN/2023/257119 Musgrave Street COOLANGATTA  QLD  4225
DAPEAAP12/07/2023 0:00MIN/2023/2572-18 Marine Parade COOLANGATTA  QLD  4225

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.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors