Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
ChenMengTing
Regular Visitor

How can I compare the monthly data with the certain year like 2019?

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 

2019 = CALCULATE(
    SUM('6_Tourism_Japan'[Visitor Arrivals]),
    YEAR('6_Tourism_Japan'[Date])=2019)
 
But I have no idea how to do it next step.  Please can anyone help me on this.
Thank you so much.
 
Best Regards,
Sunny 🙂
3 ACCEPTED SOLUTIONS
RossEdwards
Solution Sage
Solution Sage

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

View solution in original post

rajendraongole1
Super User
Super User

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





View solution in original post

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.

View solution in original post

5 REPLIES 5
rajendraongole1
Super User
Super User

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





Did I answer your question? Mark my post as a solution!

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.

RossEdwards
Solution Sage
Solution Sage

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 @RossEdwards 

 

Thank you so much, it works now!!!

 

Best Regards,

MengTing

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.