Year to date over year comparison

[jbetkowskawbd]

Hello,

I have the following issue: we need to present year over year comparison but for year to date value based on date selected by user. Meaning if user will set up on slicer from 1/1/2022 till 5/11/2024 on chart they will see 3 bars that will show totals for 1/1/2022 to 5/11/2024, for 1/1/2023 to 5/11/2023 and 1/1/2024 to 5/11/2024. But if they will change max date for for example  11/11/2024 they would see 3 bars 1/1/2022 to 11/11/2022, 1/1/2023 to 11/11/2023, 1/1/2024 to 11/11/2024.

So I prepared measure that takes maximum valuse from date slicer and additional column that will collect data in groups:

Measure:  Max Selected Date = MAXX(ALLSELECTED('pagerduty ox_calendar'[calendar_date]), 'pagerduty ox_calendar'[calendar_date])

Column: 

YTD Date =
VAR MaxDate = [Max Selected Date]
VAR CreatedDate = 'blended inc/task'[created_date]
VAR MaxYear = YEAR(MaxDate)
VAR MaxMonth = MONTH(MaxDate)
VAR MaxDay = DAY(MaxDate)
VAR CreatedYear = YEAR(CreatedDate)
VAR CreatedMonth = MONTH(CreatedDate)
VAR CreatedDay = DAY(CreatedDate)

RETURN
IF (
CreatedMonth < MaxMonth,
DATE(CreatedYear, MaxMonth, MaxDay),
IF (
CreatedMonth = MaxMonth && CreatedDay <= MaxDay,
DATE(CreatedYear, MaxMonth, MaxDay),
BLANK()
)
)

However even if measure max selected date is shown in the table proper value - max date selected on slicer, column with grouping insert values not based on max from slicer but max from all dates. How can I change this metric or column logic? This is a part of wider solution and I need to have this data grouped by dates. Also - I tried to use function previousyearsameperiod - however it works only if I have selected on slicer this year and previous one. When I select also 2022 it crashes.

Catalog with file and anonymized data 

[v-mengmli-msft · Accepted solution]

Hi @jbetkowskawbd ,

Do you mean that YTD Date should return Year of created_date and Month and Day of max selected date if created_date less than max selected date?

I think the problem is caused by you are using calculated column, and the calculated column won't get selected value of the slicer. So, calculated column is calculating the date table that is not filtered. I think the easiest way to do this is to create measure. When I create measure with your DAX, it seems to work fine.

Best regards,

Mengmeng Li

[Kedar_Pande]

@jbetkowskawbd 

Max Selected Date = MAXX(ALLSELECTED('pagerduty ox_calendar'[calendar_date]), 'pagerduty ox_calendar'[calendar_date])

YTD Date =
VAR MaxDate = [Max Selected Date]
VAR CreatedDate = 'blended inc/task'[created_date]
VAR MaxYear = YEAR(MaxDate)
VAR MaxMonth = MONTH(MaxDate)
VAR MaxDay = DAY(MaxDate)
VAR CreatedYear = YEAR(CreatedDate)
VAR CreatedMonth = MONTH(CreatedDate)
VAR CreatedDay = DAY(CreatedDate)

RETURN
IF (
CreatedYear < MaxYear, 
DATE(CreatedYear, 12, 31), 
IF (
CreatedYear = MaxYear && CreatedMonth < MaxMonth, 
DATE(CreatedYear, MaxMonth, MaxDay), 
IF (
CreatedYear = MaxYear && CreatedMonth = MaxMonth && CreatedDay <= MaxDay, 
DATE(CreatedYear, MaxMonth, MaxDay), 
BLANK()
)
)
)

💌If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[bhanu_gautam]

@jbetkowskawbd , Try using measure instead of calculated columns like

Create a measure to get the maximum selected date:
MaxSelectedDate = MAXX(ALLSELECTED('pagerduty ox_calendar'[calendar_date]), 'pagerduty ox_calendar'[calendar_date])

Create a measure to calculate the Year-to-Date (YTD) value for each year:
YTDValue =
VAR MaxDate = [MaxSelectedDate]
VAR MaxYear = YEAR(MaxDate)
VAR MaxMonth = MONTH(MaxDate)
VAR MaxDay = DAY(MaxDate)
RETURN
SUMX(
FILTER(
'blended inc/task',

Create a measure to calculate the YTD value for the previous year:
YTDValuePreviousYear =
VAR MaxDate = [MaxSelectedDate]
VAR MaxYear = YEAR(MaxDate) - 1
VAR MaxMonth = MONTH(MaxDate)
VAR MaxDay = DAY(MaxDate)
RETURN
SUMX(
FILTER(
'blended inc/task',

Create a measure to calculate the YTD value for two years ago:
YTDValueTwoYearsAgo =
VAR MaxDate = [MaxSelectedDate]
VAR MaxYear = YEAR(MaxDate) - 2
VAR MaxMonth = MONTH(MaxDate)
VAR MaxDay = DAY(MaxDate)
RETURN
SUMX(
FILTER(
'blended inc/task',

Create a measure to display the YTD values for the selected years:
YTDComparison =
UNION(
SELECTCOLUMNS(
ADDCOLUMNS(
VALUES('pagerduty ox_calendar'[calendar_year]),
"YTDValue", [YTDValue]
),
"Year", 'pagerduty ox_calendar'[calendar_year],
"YTDValue", [YTDValue]

Now you can use the YTDComparison measure in your visualizations to show the year-over-year comparison based on the user-selected date range. This approach ensures that the calculations are dynamic and respond to the slicer selections.