Forum Discussion

CPaceFOTL's avatar
CPaceFOTL
Frequent Visitor
8 years ago
Solved

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

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 ...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    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
    )

    New_Column_with_value_of_another_table_based_on_a_date_range_from_date_in_initial_table

     

    Best Regards,

    Dale

  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi CPaceFOTL,

     

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

    New_Column_with_value_of_another_table_based_on_a_date_range_from_date_in_initial_table2

     

     

    Best Regards,

    Dale

v-jiascu-msft's avatar
v-jiascu-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Hi CPaceFOTL,

 

Can you share your pbix file?

 

Best Regards,

Dale

CPaceFOTL's avatar
CPaceFOTL
Frequent Visitor
8 years ago

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's avatar
    CPaceFOTL
    Frequent Visitor
    8 years ago

    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