Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi, everyone,
I am a newer in DAX so maybe my question has a simple answer, but I have been struggling with it for so long and I do not find it. ><
I would like to compare the inbound visitors from years 2020, 2021, 2022, 2023, 2024 with the certain 2019.
I already have a measure as below
Solved! Go to Solution.
First off. I'd recommend making it dynamic, so you could have 1 measure work with any circumstance. For this you would need 2 tables, one for the dates in your FACT table and the other to be you "compare to" selection.
The easist method is to create a DateTable in dax using this guide. Link its "date" column to link your 6_Tourism_Japan[Date] field
Creating a simple date table in DAX - SQLBI
For the compare 2 table, you just need a list of years which you could use "Enter Data" for. This table does not need to be linked via any table relationships.
On your report, have a slicer which uses your unlinked years table (which i'll call Year in my example).
Then have a table visual with the first column being "Year" from your dates table and the following measure:
Arrival compare = var selectedYear = SELECTEDVALUE(Year[Year])
var dataYear = SELECTEDVALUE = Dates[Year]
var selectedResult = CALCULATE(
SUM('6_Tourism_Japan'[Visitor Arrivals]),
YEAR('6_Tourism_Japan'[Date]) = selectedYear
)
var dataResult = CALCULATE(
SUM('6_Tourism_Japan'[Visitor Arrivals]),
YEAR('6_Tourism_Japan'[Date]) = dataYear
)
RETURN
// Put your comparison here
// This might be dataResult - selectedResult
Hi @ChenMengTing - You can parameterize it to make it easier to use for different years.
Like
Visitors_By_Year =
VAR SelectedYear = SELECTEDVALUE('6_Tourism_Japan'[Year])
RETURN
CALCULATE(
SUM('6_Tourism_Japan'[Visitor Arrivals]),
YEAR('6_Tourism_Japan'[Date]) = SelectedYear
)
Create measures to compare each year's visitors with the 2019 visitors
Compare_Visitors_2020 =
CALCULATE(
SUM('6_Tourism_Japan'[Visitor Arrivals]),
YEAR('6_Tourism_Japan'[Date]) = 2020
) - [Visitors_2019]
implement the same for 2021, 2022,2023 and 2024 as like above 2020 comparsion.
Create a measure to calculate and compare each year's visitors with the 2019 baseline
Visitors_By_Year =
VAR SelectedYear = SELECTEDVALUE('Date'[Year])
RETURN
CALCULATE(
SUM('6_Tourism_Japan'[Visitor Arrivals]),
YEAR('6_Tourism_Japan'[Date]) = SelectedYear
)
Comparison_With_2019 =
VAR SelectedYear = SELECTEDVALUE('Date'[Year])
VAR Visitors_SelectedYear =
CALCULATE(
SUM('6_Tourism_Japan'[Visitor Arrivals]),
YEAR('6_Tourism_Japan'[Date]) = SelectedYear
)
VAR Visitors_2019 =
CALCULATE(
SUM('6_Tourism_Japan'[Visitor Arrivals]),
YEAR('6_Tourism_Japan'[Date]) = 2019
)
RETURN
Visitors_SelectedYear - Visitors_2019
select the year as a slicer and compare it with 2019
Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!
Proud to be a Super User! | |
Hi,
Thanks for the solution @rajendraongole1 and @RossEdwards provided, and i want to offer some more information for user to refer to.
hello @ChenMengTing , based on the code, you can try the following
Comparison_With_2019 =
VAR SelectedYear =
SELECTEDVALUE ( 'Date'[Year] )
VAR Visitors_SelectedYear =
CALCULATE (
SUM ( '6_Tourism_Japan'[Visitor Arrivals] ),
YEAR ( '6_Tourism_Japan'[Date] ) = SelectedYear
)
RETURN
Visitors_SelectedYear - [2019](the measure you created)
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @ChenMengTing - You can parameterize it to make it easier to use for different years.
Like
Visitors_By_Year =
VAR SelectedYear = SELECTEDVALUE('6_Tourism_Japan'[Year])
RETURN
CALCULATE(
SUM('6_Tourism_Japan'[Visitor Arrivals]),
YEAR('6_Tourism_Japan'[Date]) = SelectedYear
)
Create measures to compare each year's visitors with the 2019 visitors
Compare_Visitors_2020 =
CALCULATE(
SUM('6_Tourism_Japan'[Visitor Arrivals]),
YEAR('6_Tourism_Japan'[Date]) = 2020
) - [Visitors_2019]
implement the same for 2021, 2022,2023 and 2024 as like above 2020 comparsion.
Create a measure to calculate and compare each year's visitors with the 2019 baseline
Visitors_By_Year =
VAR SelectedYear = SELECTEDVALUE('Date'[Year])
RETURN
CALCULATE(
SUM('6_Tourism_Japan'[Visitor Arrivals]),
YEAR('6_Tourism_Japan'[Date]) = SelectedYear
)
Comparison_With_2019 =
VAR SelectedYear = SELECTEDVALUE('Date'[Year])
VAR Visitors_SelectedYear =
CALCULATE(
SUM('6_Tourism_Japan'[Visitor Arrivals]),
YEAR('6_Tourism_Japan'[Date]) = SelectedYear
)
VAR Visitors_2019 =
CALCULATE(
SUM('6_Tourism_Japan'[Visitor Arrivals]),
YEAR('6_Tourism_Japan'[Date]) = 2019
)
RETURN
Visitors_SelectedYear - Visitors_2019
select the year as a slicer and compare it with 2019
Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!
Proud to be a Super User! | |
Hi Sir,
Thank you for the reply.
But I made a problem when I am trying to follow the below steps.
Comparison_With_2019 =
VAR SelectedYear = SELECTEDVALUE('Date'[Year])
VAR Visitors_SelectedYear =
CALCULATE(
SUM('6_Tourism_Japan'[Visitor Arrivals]),
YEAR('6_Tourism_Japan'[Date]) = SelectedYear
)
It shows a warning that " The syntax for ')' is incorrect. (DAX(VAR SelectedYear = SELECTEDVALUE('6_Tourism_Japan'[Date].[年])VAR Visitors_SelectedYear =CALCULATE(SUM('6_Tourism_Japan'[Visitor Arrivals]),YEAR('6_Tourism_Japan'[Date]) = SelectedYear))).
Could you let me know the reasons?
Thank you!
Hi,
Thanks for the solution @rajendraongole1 and @RossEdwards provided, and i want to offer some more information for user to refer to.
hello @ChenMengTing , based on the code, you can try the following
Comparison_With_2019 =
VAR SelectedYear =
SELECTEDVALUE ( 'Date'[Year] )
VAR Visitors_SelectedYear =
CALCULATE (
SUM ( '6_Tourism_Japan'[Visitor Arrivals] ),
YEAR ( '6_Tourism_Japan'[Date] ) = SelectedYear
)
RETURN
Visitors_SelectedYear - [2019](the measure you created)
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
First off. I'd recommend making it dynamic, so you could have 1 measure work with any circumstance. For this you would need 2 tables, one for the dates in your FACT table and the other to be you "compare to" selection.
The easist method is to create a DateTable in dax using this guide. Link its "date" column to link your 6_Tourism_Japan[Date] field
Creating a simple date table in DAX - SQLBI
For the compare 2 table, you just need a list of years which you could use "Enter Data" for. This table does not need to be linked via any table relationships.
On your report, have a slicer which uses your unlinked years table (which i'll call Year in my example).
Then have a table visual with the first column being "Year" from your dates table and the following measure:
Arrival compare = var selectedYear = SELECTEDVALUE(Year[Year])
var dataYear = SELECTEDVALUE = Dates[Year]
var selectedResult = CALCULATE(
SUM('6_Tourism_Japan'[Visitor Arrivals]),
YEAR('6_Tourism_Japan'[Date]) = selectedYear
)
var dataResult = CALCULATE(
SUM('6_Tourism_Japan'[Visitor Arrivals]),
YEAR('6_Tourism_Japan'[Date]) = dataYear
)
RETURN
// Put your comparison here
// This might be dataResult - selectedResult
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
142 | |
77 | |
63 | |
51 | |
47 |
User | Count |
---|---|
214 | |
84 | |
61 | |
61 | |
60 |