Forum Discussion

Nicpet0's avatar
Nicpet0
Frequent Visitor
1 year ago
Solved

Comparing values with the same date range

Hi Community I would like to get some help with a DAX measure. I want to have a bar chart like shown in the snippet, where i compare values from previous years, but my struggle is to have the same...
comparison
date range
DAX
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Nicpet0 

     

    Here's my solution, hope it helps!

    1. Create a calculated column in the calendar table that marks whether each date falls within the same date range for the current year.

     

    IsInCurrentYearRange = 
VAR CurrentYear = YEAR(MAX('Table'[Date]))
VAR CurrentMonth = MONTH(MAX('Table'[Date]))
VAR MaxCurrentDate = DAY(MAX('Table'[Date]))
RETURN 
IF(
    'Calendar'[Year] < CurrentYear &&
    'Calendar'[Month] == CurrentMonth &&
    'Calendar'[Day] > MaxCurrentDate,
    0,
    1
)

     

    2. Create a measure to calculate revenue in the same date range.

     

    SameDateRangeRevenue = 
CALCULATE(
    SUM('Table'[Value]),
    FILTER(
        'Calendar',
        'Calendar'[IsInCurrentYearRange] = 1
    )
)

     

    3. Here is final result.

     

    Best Regards,
    Jarvis Tang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

123abc's avatar
123abc
Community Champion
1 year ago

Step 1: Create a measure that captures the latest date selected in the slicer for the current year:

MaxCurrentYearDate =
CALCULATE(
MAX('Calendar'[Date]),
'Calendar'[Year] = MAX('Calendar'[Year])
)

 

Step 2: Calculate Previous Years up to the Same Date
Now create another measure to calculate the revenue for previous years but only up to the maximum date of the current year:

 

RevenueSamePeriod =
CALCULATE(
SUM('Sales'[Revenue]),
FILTER(
ALL('Calendar'),
'Calendar'[Year] < MAX('Calendar'[Year]) &&
'Calendar'[MonthDay] <= DAY([MaxCurrentYearDate])
)
)

 

  1. In this measure:

    • ALL('Calendar') removes the date filter context so that it applies the logic across all years.
    • 'Calendar'[Year] < MAX('Calendar'[Year]) ensures it only applies to previous years.
    • 'Calendar'[MonthDay] <= DAY([MaxCurrentYearDate]) limits the comparison to the same number of days as in the current year.

  2. Step 3: Apply this Measure in Your Bar Chart
    Use the new RevenueSamePeriod measure for the previous years, so the bar chart will only display values up to the same date range as in the current year.

By using this approach, each year's data will be restricted to the same range of days as in the current year, ensuring a fair comparison across different years based on the slicer selection.