Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
103 | |
101 | |
76 | |
66 | |
60 |
User | Count |
---|---|
142 | |
106 | |
103 | |
85 | |
70 |