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