Adding a whole number to a date results in weird results

[kwong]

Hi BI Gurus!

I have two tables, related 1 - many.

Table1, DATES     Table2, Whole Numbers

06/04/2020         3

Using NewDate = SUMX('table1', 'table1[DATES] + DAY('Table2'[Whole Numbers]))

Results: 06/14/4068

Desired Result: 06/07/2020

What did I miss?  I also tried DATEADD and and recieved errors.

Any help is geatly appreciated.

[jairoaol]

I see two mistakes in your problem

• You add to a date a number of X days. you shouldn't do it like this. you must use aggregation functions like Dateadd
• You use the DAY function for inside it has a field of type number. The DAY function must have a date-type field inside.

[FrankAT]

Hi @kwong 

here a solution with parameter:

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[kwong]

Thanks!  Will this work for adding 'random' data from table2[duration]?

[nandic]

@kwong ,
First of all, day function will result in error as it expects date field and it receives whole number.
I created demo data with same structure.
Table1 = date table, Table2 = table with date column and whole number column.
Relationship is based on date column, 1 to many.

In Table1 i created new column: Using NewDate = CALCULATE(SUM(Table2[Whole Number]))
For each day it calculates total of column Whole number from Table2.
And then i add calculation to add this number of days to date column.
So final calculation in column is: Using NewDate = CALCULATE(SUM(Table2[Whole Number])) + Table1[Date]



This is data in Table2:

[kwong]

Hi, Thanks for the quick response.  This looks pretty close.  I think I should provide more context...

Table2[Duration] is a Measure from a column in a third table used to calculate averages of groups of distinct values two other columns in  table3.

Table1[Date]     Table2[Duration]

7/31/2020         0

6/4/2020           3

5/5/2020           3

6/1/2020           12 

8/6/2020           9

9/8/2020           11

8/26/2020

9/2/2020           2

The desire is to add the values in Table2[Duration] to the Dates in Table1[Date]

Return: Table1 or Table2 [Projection]    (blanks may be ignored)

Any ideas?

[jairoaol · Accepted solution]

if I understand right.

Do you want a DATE column in TABLE1 to add values that are calculated by a measure that gets values from TABLE2?

answer: It is NOT possible directly.

DAX formulas do not allow you to combine column data with measurements sirectally without using aggregations (e.g. SUMX).

rethink how to address the problem, such as:

• That calculations be made at the level of measures or columns in both cases.
• Create a calculated table with Summarize that allows you to convert the measure to a column

[kwong]

Thanks!  I think I will try to create a new table from infomration from two seprate tables.

table_current and table_past

table_combined will have [date] from table_current and average_duration from table_past

hopefully this will be fairly straight forward.  

any suggestions are welcome! you folks are great!