Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Justair07
Resolver I
Resolver I

Filter by Previous Working Day

Hello,

 

I need this DAX to look at a column for the previous working day and is the value is greater than 0 than return the value for the previous working day, otherwise return 0.

 

Daily VOC = 
IF (
    CALCULATE (
        ( SUM ( tblKPIData[VOC (RMA)] ) ),
        tblDates[Date]
            = IF (
                WEEKDAY ( TODAY (), 3 ) < 4,
                TODAY () - 1,
                TODAY ()
                    - ( 7 + WEEKDAY ( TODAY (), 3 ) )
            )
    ) > 0,
    CALCULATE (
        ( SUM ( tblKPIData[VOC (RMA)] ) ),
        tblDates[Date]
            = IF (
                WEEKDAY ( TODAY (), 3 ) < 4,
                TODAY () - 1,
                TODAY ()
                    - ( 7 + WEEKDAY ( TODAY (), 3 ) )
            )
    ),
    0
)

 My problem is that the DAX to filter by previous working day isn't correct. Apprently it is looking at March 11th (ten days in the past) not 1 buisness day in the past.

IF (
                WEEKDAY ( TODAY (), 3 ) < 4,
                TODAY () - 1,
                TODAY ()
                    - ( 7 + WEEKDAY ( TODAY (), 3 ) )
            )

How can I fix this to look at the previous working day?

 

I've used this DAX to create a COLUMN for returning the previous working day, but that does me no good in a CALCUALTE filter becasue it is a COLUMN and not a MEASURE.

 

PreviousWorkDay = 
MAXX(
		FILTER('tblDates','tblDates'[Date] < EARLIER('tblDates'[Date])
			&& WEEKDAY('tblDates'[Date],3) <5), /*0=Monday, 6=Sunday --> hence < 5 is Monday to Friday*/
		'tblDates'[Date]
        )

Maybe someone has an alternative solution for me.

 

Thank you,

 

- Justair07

1 ACCEPTED SOLUTION

@Anonymous Hello, bacically I just wanted the DAX to show previous working day. For example since today is Tuesday 3/26/2019 the returned date should be Monday 3/25/2019. If to day was Monday 3/25/2019 then the returned value should be Friday 3/22/2019. I think I have it now, What do you think? I needed to change the 4 to a 5.

IF (
                WEEKDAY ( TODAY (), 3 ) < 5,
                TODAY () - 1,
                TODAY ()
                    - ( 7 + WEEKDAY ( TODAY (), 3 ) )
            )

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

Hi,

 

It would be helpful if you provided a sample of your data to demonstrate what you're looking to do.

 

Thanks,

Ben

@Anonymous Hello, bacically I just wanted the DAX to show previous working day. For example since today is Tuesday 3/26/2019 the returned date should be Monday 3/25/2019. If to day was Monday 3/25/2019 then the returned value should be Friday 3/22/2019. I think I have it now, What do you think? I needed to change the 4 to a 5.

IF (
                WEEKDAY ( TODAY (), 3 ) < 5,
                TODAY () - 1,
                TODAY ()
                    - ( 7 + WEEKDAY ( TODAY (), 3 ) )
            )

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.