Forum Discussion

ATREZISE's avatar
ATREZISE
Helper I
7 years ago
Solved

Merge rows based on LE criteria

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.

Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    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

     

3 Replies

    • ATREZISE's avatar
      ATREZISE
      Helper I
      7 years ago

       

      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's avatar
        v-cherch-msft
        Microsoft Employee
        7 years ago

        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