DAX Measure assistance

[batman]

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

[Adamboer]

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)
- CALCULATE(SUM('YourTable'[Value]), 'YourTable'[Date] = AdjustedDate)

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.

[batman]

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

[rajulshah]

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

[batman]

No Luck

[katika555]

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.