Comparing two list from a single table

[RVLee]

Hi,

I'm trying to get a list of province up to current year and to be compared with a list of province up to last year, which eventually to get the new province name occurred in the current year.

Which is looked like below

Table: Location Comparison

And I expect to get the CY Province where LY Province is blank.

There are 1 fact table and 1 dimension table used that is related with this requirement. The fact table is the list of location and its start date. The dimension table is the location list and its province.

For example

     

Note: let's assume that current year (CY) is 2019

I'm looking for an efficient way to get the result. Tried to create a single DAX to compare the lists, by using 2 variable tables summarizing the CY Province and LY Province. However, I stuck at combining the two variable tables in the DAX.

Hope to get help on this. More efficient ways are welcomed.

Btw, here's what I tried - which is still returning an error DAX.

Summary Table =

VAR _maxDate =

MAX ( 'Location'[Location Start Date] )

VAR _CYtable =

SUMMARIZECOLUMNS (

'Location List'[Province],

FILTER (

'Location',

'Location'[Location Start Date]

<= _maxDate

)

)

VAR _maxLastYearDate =

DATE (

YEAR ( _maxDate )

- 1,

12,

31

)

VAR _LYtable =

SELECTCOLUMNS (

SUMMARIZECOLUMNS (

'Location List'[Province],

FILTER (

'Location',

'Location'[Location Start Date]

<= _maxLastYearDate

)

),

"LY Province", [Province]

)

RETURN

NATURALLEFTOUTERJOIN (

_CYtable,

_LYtable

)

[v-yalanwu-msft · Accepted solution]

Hi, @RVLee ,

Try it.

Summary Table = 
VAR _maxDate =
    MAX ( 'Location'[Start Date])
VAR _CYtable =
    SUMMARIZECOLUMNS (
        'Location List'[Province],
        FILTER ( 'Location', 'Location'[Start Date] <= _maxDate )
    )
VAR _maxLastYearDate =
    DATE ( YEAR ( _maxDate ) - 1, 12, 31 )
VAR _LYtable =
    SELECTCOLUMNS (
        SUMMARIZECOLUMNS (
            'Location List'[Province],
            FILTER ( 'Location', 'Location'[Start Date] <= _maxLastYearDate )
        ),
        "LY Province", [Province]
    )
RETURN
EXCEPT(  _CYtable, _LYtable )

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[RVLee]

Hi @v-yalanwu-msft,

It works! Thanks a lot!

Regards