cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Frequent Visitor

## DAX Measure assistance

Hello All,

I am trying to do a dax day over day calculation, comparison for last year. An example the Monday of 1/2/2023 I need it compared to Monday of 1/3/2022, because the 1/2/2022 is a Sunday, I need it compared to the following Monday.

Any tips and advice would be greatly apricated.

Thank you

B

5 REPLIES 5
Responsive Resident

It sounds like you're looking to create a day-over-day comparison for the same day of the week, but in the previous year. In your example, you're looking to compare the Monday of 1/2/2023 to the Monday of 1/3/2022. However, since 1/2/2022 is a Sunday, you need to compare it to the following Monday.

To accomplish this in DAX, you can use the DATEADD function to subtract one year from the current date and then adjust the date to the nearest Monday. You can then use this adjusted date in your comparison calculation.

Here's an example DAX formula that should achieve what you're looking for:

Last Year Comparison =
VAR CurrentDate = MAX('YourTable'[Date])
VAR LastYearDate = DATEADD(CurrentDate, -1, YEAR)
VAR AdjustedDate = LastYearDate - MOD(WEEKDAY(LastYearDate), 7) + 2
RETURN
CALCULATE(SUM('YourTable'[Value]), 'YourTable'[Date] = CurrentDate)

This formula first calculates the current date and subtracts one year to get the corresponding date in the previous year. It then adjusts the date to the nearest Monday by subtracting the weekday number and adding 2. Finally, it uses the adjusted date and the current date in a CALCULATE function to perform the day-over-day comparison.

I hope this helps! Let me know if you have any further questions or need additional assistance.

Frequent Visitor

@Adamboer  - Thank you for responding, I keep getting the following error, "The first argument to "DATEADD" must be specif a column.

Super User

@batman
You can use the following calculated column:

``````ThisOrNextWorkingDay =
VAR NextWorkingDay =
MINX (
FILTER (
Dates,
Dates[Date] > EARLIER ( Dates[Date] )
&& Dates[IsWeekend] = 0
),
Dates[Date]
)
RETURN
IF ( Dates[IsWeekend] = 1, NextWorkingDay, Dates[Date] )``````

Please let me know if this didn't work.

Frequent Visitor

No Luck

Resolver I

Hi,
I think you can add days to Date[date] column and match first Monday of both years and calculated by this new column second measure.

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.