Skip to main content
cancel
Showing results for 
Search instead 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

Reply
batman
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
Adamboer
Responsive Resident
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)
- 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.

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

batman_0-1682360214358.png

 

rajulshah
Super User
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.

No Luck

katika555
Resolver I
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.

Helpful resources

Announcements
Fabric Community Conference

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.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

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

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.