Merge rows based on LE criteria

[ATREZISE]

Is it possible to use query editor to merge two tables based on any other criteria than matching columns?

I'd like to merge the contents on two tables and extract data from the second table based on a date being <= a date in the first.

i.e. give me all the records in t1 plus the cumulative revenue from t2 based on the date in t2 being <= the date in t1

Something like this - forgive me if this is slightly incorrect syntax...it's been a while since I've written SQL

SELECT t1.*,sum(t2.revenue) from t1 JOIN t2 on t1.project = t2.project and t2.date <= t1.date

I have tried (using measures) in my report to do a similar kind of thing an had some success but I came unstuck when displaying totals / sub-totals....they just didn't equate properly so I'd like to take a slightly different tact if possible and pull the data directly into my base table.

Thanks in advance.

[Stachu]

can yuo share sample data?

[ATREZISE]

Two tables….Project Status and Project Actuals – not linked

Project Status is used to capture RAG status against each sub-project on a month by month basis…

Project  Group     Sub-Project       Year       Month   Status                   Risk                       Budget             Scope

SA06                     JI01-001           2018      08           Green                   Green                   Green               Green

SA06                     JI01-001           2018      09           Green                   Green                   Green               Amber

SA06                     SA06-001         2018      08           Green                   Green                   Green               Green

SA06                     SA06-001         2018      09           Amber                  Amber                  Green               Green

Project Actuals contains the revenue captured against each project month by month

Project  Group      Sub-Project         Year       Month                  Revenue           

SA06                     JI01-001              2018      08                          £120,700          

SA06                     JI01-001              2018      09                          £129,060

SA06                     SA06-001            2018      08                          £102,340         

SA06                     SA06-001            2018      09                          £2,030,438

The measure I use to pull the data together is as follows…I create a column on each table to store the actual date based on 01/MONTH/YEAR

Cumulative AX Total Revenue = CALCULATE ( SUM('Project Actuals'[Revenue]), FILTER ( ALL ( 'Project Actuals' ), 'Project Actuals'[Sub-Project] = MIN ( 'Project Status'[Sub-Project] ) && 'Project Actuals'[TimeByDay] <= EOMONTH( MIN ( 'Project Status'[TimeByDay] ), 0 ) ), ALL ( 'Project Status' ) )

[v-cherch-msft · Accepted solution]

Hi @ATREZISE

You may refer to below measure :

Cumulative AX Total Revenue =
CALCULATE (
    SUM ( 'Project Actuals'[Revenue] ),
    FILTER (
        ALL ( 'Project Actuals' ),
        'Project Actuals'[Sub-Project] = MAX ( 'Project Actuals'[Sub-Project] )
            && 'Project Actuals'[Month] <= 'Project Actuals'[Month]
    )
)

Rank = RANKX(ALL('Project Actuals'),CALCULATE(MAX('Project Actuals'[Sub-Project])),,ASC,Dense)

Final =
IF (
    ISFILTERED ( 'Project Actuals'[Project  Group] ),
    [Cumulative AX Total Revenue],
    CALCULATE (
        [Cumulative AX Total Revenue],
        FILTER ( 'Project Actuals', [Rank] = 1 )
    )
)

Regards,

Cherie