Forum Discussion

SteenSoernesen's avatar
SteenSoernesen
Frequent Visitor
1 year ago
Solved

Compare passenger numbers

Hi 

 

I'm looking a DAX formula that allows me to compare a quarter from 2 different years, so I can show the increase or decrease in %

 

Any ideas to a useful DAX formula?

  • v-veshwara-msft's avatar
    v-veshwara-msft
    1 year ago

    Hi SteenSoernesen ,
    Thanks for your follow-up. Since your dataset contains only Year, Quarter, and Passenger Count (without a full date column), you can still achieve a year-over-year quarter comparison by adding a calculated column to extract the numeric quarter and using measures to calculate the % change.Here's a step by step approach:

    Assuming the data table name is PassengerData
    1. Create a numeric quarter column to convert "1. Kvartal" to 1, and so on:

    QuarterNumber =

    SWITCH(
        TRUE(),
        SEARCH("1", 'PassengerData'[Kvartal], 1, 0) > 0, 1,
        SEARCH("2", 'PassengerData'[Kvartal], 1, 0) > 0, 2,
        SEARCH("3", 'PassengerData'[Kvartal], 1, 0) > 0, 3,
        SEARCH("4", 'PassengerData'[Kvartal], 1, 0) > 0, 4
    )
     
     
    2. Measure to get total passengers:
    Total Passengers = SUM('PassengerData'[Passagertal])
     
    3. Measure to get passengers from the same quarter last year:
    Passengers Last Year Same Quarter =
    VAR CurrentYear = MAX('PassengerData'[År])
    VAR CurrentQuarter = MAX('PassengerData'[QuarterNumber])
    RETURN
    CALCULATE(
        [Total Passengers],
        FILTER(
            ALL('PassengerData'),
            'PassengerData'[År] = CurrentYear - 1 &&
            'PassengerData'[QuarterNumber] = CurrentQuarter
        )
    )
     
    4. Measure to calculate % change:
    % Change vs Last Year Same Quarter =
    DIVIDE(
        [Total Passengers] - [Passengers Last Year Same Quarter],
        [Passengers Last Year Same Quarter],
        0
    )
     
    Once these are in place, you can use a Matrix visual with Year and Quarter to show the results as shown below:

    Also, to Format % Change vs Last Year Same Quarter as percentage: Go to Model view --> select the measure --> Format as Percentage with 1 decimal place.

     

    You can also apply conditional formatting to the % change measure to highlight increases and decreases.

     

    Hope this helps. Please reach out for further assistance.
    If this post helps, then please consider to give a kudos and Accept as the solution to help the other members find it more quickly.


    Thank you.

6 Replies

  • anilelmastasi's avatar
    anilelmastasi
    Super User
    1 year ago

    If you want to calculate it manually, you can simply use:

    Sales CY Quarter =
    CALCULATE(
    [Total Sales],
    FILTER(
    ALL('Date'),
    'Date'[Year] = MAX('Date'[Year]) &&
    'Date'[Quarter] = MAX('Date'[Quarter])
    )
    )

     

    But I suggest you to this dynmically if possible:

     

    Create a “Years Ago” parameter
    Go to Modeling → New Parameter → Numeric Range.

    Set:

    Name → Years Ago

    Minimum → 1

    Maximum → 5 (or whatever range you want)

    Increment → 1

    This will create a table called Years Ago and a slicer that users can control on the report.

     

    Create the dynamic comparison measure

    Sales N Years Ago Quarter =
    VAR SelectedYearsAgo = SELECTEDVALUE('Years Ago'[Years Ago])
    VAR CurrentYear = MAX('Date'[Year])
    VAR CurrentQuarter = MAX('Date'[Quarter])
    RETURN
    CALCULATE(
    [Total Sales],
    FILTER(
    ALL('Date'),
    'Date'[Year] = CurrentYear - SelectedYearsAgo &&
    'Date'[Quarter] = CurrentQuarter
    )
    )

     

    Create the % change measure

    % Change vs N Years Ago =
    DIVIDE(
    [Total Sales] - [Sales N Years Ago Quarter],
    [Sales N Years Ago Quarter],
    0
    )

    If this solved your issue, please mark it as the accepted solution. ✅

     

     

  • sjoerdvn's avatar
    sjoerdvn
    Solution Sage
    1 year ago

    Assuming you have a measure [Sales], a date table 'Calendar' with a date column 'date' and a column that has the quarter info. If you then filter on one year and quarter, you can use the measure below to get the change from the same quarter the previous year.

    change from same quarter previous year = 
VAR cq = [Sales]
VAR pq = CALCULATE([Sales], PARALLELPERIOD(Calendar[Date],-4,quarter))
RETURN DIVIDE(cq - pq,pq,0)
  • v-veshwara-msft's avatar
    v-veshwara-msft
    Community Support
    1 year ago

    Hi SteenSoernesen ,
    Thanks for using Microsoft Fabric Community.

    Just checking in to see if you query is resolved and if any responses were helpful. If so, kindly consider marking the helpful reply as 'Accepted Solution' to help others with similar queries. 

    Otherwise, feel free to reach out for further assistance.

    Thank you.

    Also thanks to sjoerdvn and anilelmastasi for your prompt responses.

  • v-veshwara-msft's avatar
    v-veshwara-msft
    Community Support
    1 year ago

    Hi SteenSoernesen ,

    Following up to see if your query has been resolved. If any of the responses helped, please consider marking the relevant reply as the 'Accepted Solution' to assist others with similar questions.

    If you're still facing issues, feel free to reach out.

    Thank you.

  • SteenSoernesen's avatar
    SteenSoernesen
    Frequent Visitor
    1 year ago

    Looking at the incoming data. I don't have a date Column I can use.

    I only have a quarter and year Columns

     

    • v-veshwara-msft's avatar
      v-veshwara-msft
      Community Support
      1 year ago

      Hi SteenSoernesen ,
      Thanks for your follow-up. Since your dataset contains only Year, Quarter, and Passenger Count (without a full date column), you can still achieve a year-over-year quarter comparison by adding a calculated column to extract the numeric quarter and using measures to calculate the % change.Here's a step by step approach:

      Assuming the data table name is PassengerData
      1. Create a numeric quarter column to convert "1. Kvartal" to 1, and so on:

      QuarterNumber =

      SWITCH(
          TRUE(),
          SEARCH("1", 'PassengerData'[Kvartal], 1, 0) > 0, 1,
          SEARCH("2", 'PassengerData'[Kvartal], 1, 0) > 0, 2,
          SEARCH("3", 'PassengerData'[Kvartal], 1, 0) > 0, 3,
          SEARCH("4", 'PassengerData'[Kvartal], 1, 0) > 0, 4
      )
       
       
      2. Measure to get total passengers:
      Total Passengers = SUM('PassengerData'[Passagertal])
       
      3. Measure to get passengers from the same quarter last year:
      Passengers Last Year Same Quarter =
      VAR CurrentYear = MAX('PassengerData'[År])
      VAR CurrentQuarter = MAX('PassengerData'[QuarterNumber])
      RETURN
      CALCULATE(
          [Total Passengers],
          FILTER(
              ALL('PassengerData'),
              'PassengerData'[År] = CurrentYear - 1 &&
              'PassengerData'[QuarterNumber] = CurrentQuarter
          )
      )
       
      4. Measure to calculate % change:
      % Change vs Last Year Same Quarter =
      DIVIDE(
          [Total Passengers] - [Passengers Last Year Same Quarter],
          [Passengers Last Year Same Quarter],
          0
      )
       
      Once these are in place, you can use a Matrix visual with Year and Quarter to show the results as shown below:

      Also, to Format % Change vs Last Year Same Quarter as percentage: Go to Model view --> select the measure --> Format as Percentage with 1 decimal place.

       

      You can also apply conditional formatting to the % change measure to highlight increases and decreases.

       

      Hope this helps. Please reach out for further assistance.
      If this post helps, then please consider to give a kudos and Accept as the solution to help the other members find it more quickly.


      Thank you.