New Column with value of another table based on a date range from date in initial table

[CPaceFOTL]

All,

I have been searching and looking at calculated columns and LOOKUPVALUE; however, have not gotten anything to work like I am seeing.

I have two tables (Table1 & Table2).

Table1 is a table that shows the annual cost and daily cost within a date range; example below

Start              End               Annual_Cost     Daily_Cost

1/1/2018      1/10/2018     365                    1

1/11/2018    2/1/2018       720                    2

2/2/2018      null                1440                  4

Table2 is a table that shows the summary of data with Created Date and Total Items like below

CreatedDate          Total_Items

1/1/2018                 25

1/2/2018                 35

.......

1/19/2018               50

I am wanting to add a new column to Table2, using the CreatedDate and pulling the Daily_Cost from Table1 when the CreatedDate is between the Start and End dates of Table1.  If the CreatedDate is greater or equal to the Start Date and the End Date is null (last line), then use that Daily Cost.

Any help would be greatly appreciated.

Thanks,

CPace

[v-jiascu-msft · Accepted solution]

Hi @CPaceFOTL,

Try out this formula please.

Column =
VAR temp =
    CALCULATE (
        SUM ( Table1[Daily_Cost] ),
        FILTER (
            Table1,
            'Table1'[Start] <= [CreatedDate]
                && 'Table1'[End] >= [CreatedDate]
        )
    )
RETURN
    IF (
        ISBLANK ( temp ),
        CALCULATE ( SUM ( Table1[Daily_Cost] ), 'Table1'[End] = BLANK () ),
        temp
    )

Best Regards,

Dale

[Anonymous]

I have tried the above steps but the thing is I want to fetch some column which is not  Daily_Cost but let's say a product_name which is text. Is there any way which I can try because if I use sum it will not be applied to text formatted cells... Thanks in advance.

[CPaceFOTL]

Hello @v-jiascu-msft,

Thank you for that insight.  I did that and it took the fomula; however, now it is just returning blanks for the cost and not the value from the other table.  This one is driving me crazy.

Thanks,

Craig Pace

[v-jiascu-msft]

Hi @CPaceFOTL,

Can you share your pbix file?

Best Regards,

Dale

[CPaceFOTL]

Hi @v-jiascu-msft,

It has a lot of data and very large file with some data that is not sharable; however, I think I see why it is returning blanks.  From some reason, in the data view that table is empty with 0 rows.  This is a manually created table to maintain these values while in query editor.  Trying to see why it is not building the data.

Craig Pace

[CPaceFOTL]

Hi @v-jiascu-msft,

Thank you very much for your help on this.  I had marked the table has not to load from the query editor and when I turned it back on, it actually did not load any data until I added a new row to the table.  I now have it loaded correctly (Just hidden from report view since I don't want that data seen) and it is correctly filling my column as desired.

Craig Pace

[v-jiascu-msft · Accepted solution]

Hi @CPaceFOTL,

Try out this formula please.

Column =
VAR temp =
    CALCULATE (
        SUM ( Table1[Daily_Cost] ),
        FILTER (
            Table1,
            'Table1'[Start] <= [CreatedDate]
                && 'Table1'[End] >= [CreatedDate]
        )
    )
RETURN
    IF (
        ISBLANK ( temp ),
        CALCULATE ( SUM ( Table1[Daily_Cost] ), 'Table1'[End] = BLANK () ),
        temp
    )

Best Regards,

Dale

[CPaceFOTL]

@v-jiascu-msft

I am trying to insert the new Custom Column; however, I am getting the error message Token Eof expected.  When I click on the Show error hyperlink, it is pointing me to the temp on the VAR statement.  Any ideas?

Thanks,

Craig Pace

[v-jiascu-msft · Accepted solution]

Hi @CPaceFOTL,

You need to add it in the data view rather than the Query Editor.

Best Regards,

Dale