Forum Discussion

Frequent Visitor

4 years ago

Solved

Comparing two list from a single table

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....

Need Help

v-yalanwu-msft

4 years ago

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.

Comparing two list from a single table.pbix52 KB

v-yalanwu-msft

Community Support

4 years ago

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.

Comparing two list from a single table.pbix52 KB