cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ezaidi
Frequent Visitor

Create a measure that adds a number of days to an existing column

Hi I am trying to create a new measure (not a calculated column) because I am connecting to an existing dataset that hosts a sales table. The measure I want to create called "target date" will simply take an existing date column ("poReceivedDate") and add a number of days to it, thats it. For example if poReceiveddate is 11/22/2022 and target should be poReceivedDate + 5, targetDate = 11/27/2022. This should be rather very simple however I cant seem to make my measure work. Dateadd doesnt seem to work. I have tried different variations of the following:

!Target Rev 1 Date = CALCULATE(selectedValue(SalesOrders[poReceivedDate]) + 9,ALLEXCEPT(SalesOrders,SalesOrders[salesOrder]))


!Target Rev 1 Date = CALCULATE(selectedValue(SalesOrders[poReceivedDate]) + 9)

 

ezaidi_0-1669988299073.png

 

Also is there a way to modify the date format so that it doesnt show the hours? Usually when I have control over the dataset, I can do this, but when connecting to an existing published dataset I dont seem to have any controls anymore.

8 REPLIES 8
v-yalanwu-msft
Community Support
Community Support

Hi, @ezaidi ;

You could try this measure.

!Target Rev 1 Date =
CONVERT ( FORMAT ( SalesOrders[poReceivedDate], "yyyy-mm-dd" ), DATETIME )+9

The final show:

vyalanwumsft_0-1670223027387.png

and if you want show date type, you could change here.

vyalanwumsft_1-1670223071086.png


Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi thanks for this! It works however.... I noticed some strange behavior when I used it in my report. When adding the measure alone it works however when I add a field from another table (joined of coursed), it creates a mess. As if the tables were not connected and it does n x n cardinality between both tables on that added field. Please see the following screenshots to understand what is happening.image.pngimage.png

As you can see, I cannot include both. Individually those fields work but together they wont. Not sure exatcly whats going on here...

 

Your help is very much appreciated. Thanks!

Most of the time you want to use the fields from the dimension table to force an inner join. Uncheck "Order Number"  from "Order Dates"  and check it from "OrderDesc".

I have tried switching to using the OrderNo from the dim table and still getting the same result. THe only way I seem to be able to able to get around it is by setting a visual filter to not allow 1/8/1900 (which I dont even know why specifically jan 8th 1900 is returned). My Dax formula for Target date is 

TargetDate = MAX('OrderDates'[Date])+9
ezaidi_0-1671129104682.png

 

 

Make sure that MAX('OrderDates'[Date]) is meaningful in your filter context.

 

You get 1/8/1900 because it is 9 days after the beginning of Windows time (which is 12/31/1899  for time zone reasons)

 

What is story behind December 30, 1899 as base date? (microsoft.com)

lbendlin
Super User
Super User

Measures cannot influence table columns. They can only influence visuals. In your visual you need to make sure that the display granularity is on day level.  Then you can use functions like DATEADD.  The measure will fail if you have aggregated data displayed (for example months)

Sorry, I may have not been clear. I don't want to influence the poReceived column. I simply want a measure to take the poReceiveddate value and add some days to it and display it next to it as target date. A measure should be able to do this. How else would you suggest I do this? Calculated columns are not possible because this is a direct query dataset.

Remember that measures work against a filter context, not necessarily against a single data row  (unless you specifically structure your visual for that).

 

You can use rudimentary date math similar to EDATE.  As you describe, adding an integer to a derived date will work .  DATEADD requires an entire column to work with.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.