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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kwong
Frequent Visitor

Adding a whole number to a date results in weird results

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.

1 ACCEPTED SOLUTION
jairoaol
Impactful Individual
Impactful Individual

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

View solution in original post

7 REPLIES 7
jairoaol
Impactful Individual
Impactful Individual

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
Community Champion
Community Champion

Hi @kwong 

here a solution with parameter:

 

15-09-_2020_22-54-55.png

 

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
Frequent Visitor

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

nandic
Memorable Member
Memorable Member

@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]

new date.PNG

This is data in Table2:
new date table2.PNG

kwong
Frequent Visitor

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
Impactful Individual
Impactful Individual

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

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.