topic Re: Comparing values with the same date range in DAX Commands and Tips

Comparing values with the same date range

Nicpet0 — Wed, 18 Sep 2024 10:42:42 GMT

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 date range as my current year (in this case 2024/25) so in a month slicer i have selected september. this means that i have data from september 1st til september 17th. But my previous years shows the full month of september. How can i make sure it always follows the same range? how would a DAX measure look like to achieve this?

Re: Comparing values with the same date range

123abc — Wed, 18 Sep 2024 13:00:13 GMT

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])
)
)

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

Re: Comparing values with the same date range

Anonymous — Fri, 20 Sep 2024 09:38:36 GMT

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.

Re: Comparing values with the same date range

123abc — Wed, 19 Feb 2025 09:17:31 GMT

excellent